Common Named Formula with Different Results Depending On Worksheet

[jbrouse]

Greetings, everyone. I hope everyone is having a great start to the new year.

I hope someone can help me out with this.

I have two lists of numbers on separate worksheets. I am comparing the two lists to see if a number exists in one list, but not in the other. I use conditional formatting on my worksheet, "Sales", along with the named formulas

PartNoLookup=Labor!$B$2:INDEX(Labor!$B:$B,Lastrow)
Lastrow=MATCH(REPT("z",255),Labor!$B:$B)

to highlight values in "Sales" that do not exist in "Labor".

I know that I can repeat the above with new named formulas to reference the other list, but looking at the full list of named formulas, I see potential to clean it up. Rather than have PartNoLookup, PartNoLookup2, Lastrow and Lastrow2, why not set PartNoLookup and Lastrow to be dependent on what worksheet you are on?

Thus, to start out, I tried to set

Lastrow=IF(REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")="Sales",MATCH(REPT("z",255),Labor!$B:$B),MATCH(REPT("z",255),'Sales Plan'!$B:$B))

If I place the formula itself into a cell on either worksheet, it works properly. But once I assign the name Lastrow to it, it only works on "Labor".

On Sheet "Labor", the formula itself and "=Lastrow" both result in 2123, which is correct, but on "Sales", the results are "975" (correct) and "2123" (incorrect), respectively.

Can someone please explain why "=Lastrow" would give me the incorrect result, and second, if and why this would be a bad way of doing this?

Thanks in advance!

 

[mikerickson]

Rather that tortuous use of CELLS("filename", you might be interested in the syntax for relative sheet referencing in Names

Name: myCell
RefersTo =!$A$1

(Note the use of ! )

=myCell will return A1 of the sheet which holds the formula.


The problem with your LastRow name is that all the MATCH formulas refer only to sheet Labor!

 

[jbrouse]

Thank you for your reply.

If I may, how do all of the MATCH formulas refer to only sheet Labor? If the condition is TRUE, meaning I am on sheet Sales, it should reference sheet Labor, but if the condition is FALSE, should it not reference sheet Sales based on the formula above?

Note: I do see an error in my Lastrow formula above, referencing both "Sales" and "Sales Plan". The actual formula does not contain this error.

And as for your other comment, would I simply replace CELL("filename",A1) with myCell?

Again, thank you for your reply.