Conditional Formating or List required to highlight values not pulled through that should

Thanks to some lovely people last time we got the 0, and 1 working correctly (well sort of)
But we've noticed particularly for the V7 and X8 rates that even though there might be a figure i.e 1 or 3 in the Input Template, the value isn't showing up in the Cost Calculation tab.
Is there a way to highlight these on both/either sheet so we can review them, or possibly change the fomula to make sure it's pulling through correctly as we may be missing loads of revenue we could be collecting?
I've pulled through the values into Columns AC and AD to demonstrate the costs aren't pulling through as the example but I'd need to apply this to all the columns on the page but if we can fix these two as a priority I'd be grateful!

Hi Glen,

The only thing we need to focus on is the values aren't pulling through all the time despite a number number being entered into the Input Template.
I've pulled through the values from the Cost template to show that sometimes it pulls a value and other times it doesn't.

We need a way to highlight cells where the value hasn't pulled through in a different colour (so conditional formating)
OR
create a list on a different sheet for the lines affected
OR and probably the best solution a fix on the fomulae to make sure the values are pulling through correctly so we dont need a manual amendment.

Thank you for taking a look. My brain just can't figure it out.

Hi Glen,

They're all in the COST TEMPLATE. there's several dependong on the columns:

Columns K-N (A1-A5)
=IF($B6="","",IF(VLOOKUP($B6&"-"&ROW(),inputtemplate,MATCH(J$3,'Input Template'!$J$3:$AB$3,0)+9,FALSE)>0,VLOOKUP($E6,costtemplate,MATCH(J$3,'Cost Model'!$E$1:$CN$1,0)+2,FALSE),""))

Column TMA Column O (TMA)
=IF('Input Template'!$O6="","",VLOOKUP('Input Template'!O6,TMACOSTS,2,FALSE))

Columns P-V (V1-V7)
=IF($B6="","",IF(VLOOKUP($B6&"-"&ROW(),inputtemplate,MATCH(P$3,'Input Template'!$J$3:$AB$3,0)+9,FALSE)>0,IF(VLOOKUP($E6,costtemplate,MATCH(LEFT(P$3,FIND(" ",P$3,1)-1)&VLOOKUP(MATCH(1,IF('Input Template'!$J6:$N6<>"",1,0),0),Aratelookup,2,FALSE),'Cost Model'!$E$1:$CN$1,0)+2,FALSE)="","",
VLOOKUP($E6,costtemplate,MATCH(LEFT(P$3,FIND(" ",P$3,1)-1)&VLOOKUP(MATCH(1,IF('Input Template'!$J6:$N6<>"",1,0),0),Aratelookup,2,FALSE),'Cost Model'!$E$1:$CN$1,0)+2,FALSE)*VLOOKUP($B6&"-"&ROW(),inputtemplate,MATCH(P$3,'Input Template'!$J$3:$AB$3,0)+9,FALSE)),""))

Columns W-AB (X1-X8)
=IF($B6="","",IF(VLOOKUP($B6&"-"&ROW(),inputtemplate,MATCH(W$3,'Input Template'!$J$3:$AB$3,0)+9,FALSE)>0,IF(VLOOKUP($E6,costtemplate,MATCH(LEFT(W$3,FIND(" ",W$3,1)-1)&VLOOKUP(MATCH(1,IF('Input Template'!$J6:$N6<>"",1,0),0),Aratelookup,2,FALSE),'Cost Model'!$E$1:$CN$1,0)+2,FALSE)="","",
VLOOKUP($E6,costtemplate,MATCH(LEFT(W$3,FIND(" ",W$3,1)-1)&VLOOKUP(MATCH(1,IF('Input Template'!$J6:$N6<>"",1,0),0),Aratelookup,2,FALSE),'Cost Model'!$E$1:$CN$1,0)+2,FALSE)*VLOOKUP($B6&"-"&ROW(),inputtemplate,MATCH(W$3,'Input Template'!$J$3:$AB$3,0)+9,FALSE)),""))

The file looks vaguely familiar, I think I may have looked at a previous thread when you were trying to fix a small portion of it. I never got my head around what you were trying to do.

On a general note, you seem to be asking for a formula to show when another formula isn't working, which is kind of the wrong question - you need to fix the first formula if it's not doing what you want.

Hi Glen,

So if we row 19 as an example. Cell W19 in the Cost Calculation but there's a 1 in cell W19 in the input template. It's definitely not working correctly.
The orange cell indicates that it should be pulling through.
Same issue in Cells AB9-10, AB17-AB20 - The cost calculation tab is Blank but there's a 1 in the Input Template.

I don't know what the logic is supposed to be. But for W19 you end up with this:

1. Checks row exists in inputtemplate - if not then "". This evaluates to TRUE
2. Finds first column in J:N of input template with "1", then converts this to a letter and combines with first part (before space) of value in row 3 of cost calculation. This evaluates to "X1d". Is it meant to be "X1a"?
3. Looks up value in cost model sheet based on value in column E of cost calulation sheet (3SL6) and above value (X1d). This evaluates to "". (if it was X1a then it would be 66.51)
4. If 3. is not blank, multiplies by factor looked up in input template based on the value in row 3 of the cost calculation "X1 (a-d)", this would evaluate to 1 if the result from 3. was not blank.

Hi,

Sorry for the delay, got pulled onto other stuff.
What I'm trying to say is it should pull the value for any of the X1's a-d.
Glenn I have reviewed. I simply need the sheet to pull a price from the back sheet so using Row 19 as the example again X1 has a 1 in Column W but on the Cost Calculation Tab it's showing as blank. Meaning it's not pulling through all the time. It's definitely not blank.


I'm also struggling to pull over different rates for different companies based on the Cost Calculation. I thought by changing the Sheet name and renaming the parameters it would fix that, but just coming up blank on these examples as well.

You say that you want it to "pull the value for any of the X1's a-d".

What result are you expecting when there is more than one value for these in the Cost Model sheet?