Make INDIRECT formula easier to copy ...

  • Thread starter Thread starter Trevor Shuttleworth
  • Start date Start date
T

Trevor Shuttleworth

With help from the New Groups, I have produced formulae similar to the
following:

=IF(ISERROR(VLOOKUP($A63,INDIRECT("'"&B$60&"'!$C:$G"),5,FALSE)),"",VLOOKUP($
A63,INDIRECT("'"&B$60&"'!$C:$G"),5,FALSE))

This does exactly what I want BUT I need to copy it and when I do so I need
to adjust the INDIRECT part of the formula.

I would like to make the B$60 more flexible but Excel has been less than
happy with my attempts at producing an acceptable formula. What I am trying
to get to is something like:

=IF(ISERROR(VLOOKUP($A63,INDIRECT("'"&B$" & ROW(A60)
&"'!$C:$G"),5,FALSE)),"",VLOOKUP($A63,INDIRECT("'"&B$" & ROW(A60)
&"'!$C:$G"),5,FALSE))

Syntax errors R Us !!

So, please help me to change a static "B$60" to "B$" & ROW(A60)

TIA

Regards

Trevor
 
Trevor,

Here's a shot

=IF(ISERROR(VLOOKUP($A63,INDIRECT("'"&B$"&ROW(60)&"&"'!$C:$G"),5,FALSE)),"",
VLOOKUP($
A63,INDIRECT("'"&B$"&ROW(60)&"&"'!$C:$G"),5,FALSE))
 
Hi
why not use:
=IF(ISERROR(VLOOKUP($A63,INDIRECT("'"&$B60&"'!$C:$G"),5,FALSE)),"",VLOO
KUP($
A63,INDIRECT("'"&$B60&"'!$C:$G"),5,FALSE))
 
Back
Top