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!
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!