Reference Sheets in a Vlookup
=INDIRECT("'"&A1&"-"&B1&"'!$A$13:$AL$200")
To save a couple of keystrokes you can remove the $ from $A$13:$AL$200.
Since INDIRECT evaluates this as a text string that reference will never
change if the formula is copied. So:
=INDIRECT("'"&A1&"-"&B1&"'!A13:AL200")
"'!A13:AL200" is treated as an absolute reference.
--
Biff
Microsoft Excel MVP
"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in
message ...
=INDIRECT("'"&A1&"-"&B1&"'!$A$13:$AL$200") instead of
'000-00'!$A$13:$AL$200
in your formula below, iIf you want to look in A1 and B1 to construct your
sheet name.
Basically construct a string which returns the address you want and put
that inside INDIRECT...
"Thomas Roos" wrote:
I have the following Vlookup formula
=ROUND(IFERROR(VLOOKUP($D196,'000-00'!$A$13:$AL$200,F$1,FALSE),0),0)
I want to have '000-00' change based on combining two cells.
My worksheet has 100 plus sheets and want to be able to reference the
sheet
location based on two other cells.
Is there a way to do this?
thanks
--
Tom
|