Change in Lookup to different sheet not working

[nickynelsa]

Hi Guys,

me again, so I'm trying to create a comparison of the old vs new rates.

All jobs are input into the Input Table from Columns J-W

The Old rates pull through on the Cost Calculation tab from the Cost Model tab
eg. a 3SL6 with an A1 rate should be 393.93 assuming there's a 1 in Column J-W (Cell J6)

I want the new rates to pull through on the Value Output Tab from the Rates tab.
eg. a 3SL6 with an A1 rate should bow be 173.61.
eg. a 3ML1 with an A2 rate should be 796.165 (Cell K13)

I though it would be a simple change to the named range but everytime I change Costtemplate to the new ratestemplate it's coming back as A #NA


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


=IF($B6="","",IF(VLOOKUP($B6&"-"&ROW(),inputtemplate,MATCH(J$3,'Input Template'!$J$3:$W$3,0)+9,FALSE)>0,VLOOKUP($E6,ratestemplate,MATCH(J$3,Rates!$E$1:$CN$1,0)+2,FALSE),""))

Can someone tell me what I've done wrong?

[etaf]

looking at the name ranges
the costtemplate one thats looking at the costmodel for the full workbook scope
range
='Cost Model'!$C$3:$CS$500
ratestemplate
=Rates!$A$1:$CS$203

so change rates template to
=Rates!$C$1:$CS$203

that fixes it

You have a few duplicated nameranges, with different scope
for example
costtemplate for example has 2 entries costmodel - one scope applies to the workbook, anysheet
and the other only apples to the RATES sheet scope RATES

have a read here for Named Ranges and Scope
https://exceljet.net/named-ranges#:~...y%20exist%20on.
and here
https://www.fm-magazine.com/news/201...201920560.html

Just incase you use the same name and get different results - because the global worksheet scope is different to the specific worksheet

for example
costtemplate - Worksheet scope referes to
='Cost Model'!$C$3:$CS$500
BUT
costtemplate - RATES scope referes to
=Rates!$C$3:$CS$500
so a different sheet name - when used on the RATES Sheet

[nickynelsa]

Hello!


That's what I did! but still showing as #N/A on my sheet so created a new names range and worked thank you so much!

[etaf]

you are welcome, thanks for the feedback and glad you managed to sort out