Forum Discussion

avgjoe08's avatar
avgjoe08
Copper Contributor
Oct 03, 2021

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...
excel
Formulas and Functions
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Oct 03, 2021

avgjoe08 Try this:

=LOOKUP(2,1/(INDIRECT("'"&A2&"'!J:J"<>""),INDIRECT("'"&A2&"'!J:J")
  • avgjoe08's avatar
    avgjoe08
    Copper Contributor
    Oct 09, 2021
    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"))
  • avgjoe08's avatar
    avgjoe08
    Copper Contributor
    Oct 09, 2021

    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?

Resources