Forum Discussion
Excel help - dynamic worksheet
I have the formula below and I simply want the formula to apply to a different worksheet within the same workbook based on the name in column A - rather than editing the formula for each row. ACB is a value in A2 - rather than manually editing the formula every time I copy it to a new row I would like the formula to reference the worksheet named in column A??
=LOOKUP(2,1/(ACB!J:J<>""),ACB!J:J)
3 Replies
avgjoe08 Try this:
=LOOKUP(2,1/(INDIRECT("'"&A2&"'!J:J"<>""),INDIRECT("'"&A2&"'!J:J")- Riny_van_Eekelen thanks again - slight change to what you suggested and think it is working - just needed a bracket after the J:J in the first INDIRECT reference =LOOKUP(2,1/(INDIRECT("'"&A2&"'!J:J")<>""),INDIRECT("'"&A2&"'!J:J"))
Riny_van_Eekelen thanks but I'm getting a reference error? I have pasted your suggestion just to make sure I transposed it correctly =LOOKUP(2,1/(INDIRECT("'"&A2&"'!J:J<>""),INDIRECT("'"&A2&"'!J:J"))) - in case it is relevant I haven't defined any range names or worksheet names?
