variable reference in hlookup array

[Guest]

Hi, I have many Hlookup functions in which the array is in
one of many sheets, all named with a number (say 1000).
Since there are many of these numbered sheets the
reference in the hlookup function needs to be a variable.
In column A I have the specific sheet name I need to
reference and I am hoping I can point at that cell as part
of the array address within the hlookup function. I was
trying the following but it doesn't work.

HLOOKUP(6,'A4'!$D$2:$Z$50,9)

where A4 = 9000

Please help

 

[Peo Sjoblom]

One way

=HLOOKUP(6,INDIRECT("'"&A4&"'!D2:Z50"),9)

 

[Soe]

why =HLOOKUP(6,INDIRECT(A4 & "D2:Z50"),9), A4='9000'! doesn't work?

 

[Soe]

it works in same workbook, however, when we put in path for a different
workbook, it doesn't work.
any idea why is that?

 

[Harlan Grove]

why =HLOOKUP(6,INDIRECT(A4 & "D2:Z50"),9), A4='9000'! doesn't work?

....

Does A4 appear as '9000'! or as 9000'! ? If the former, the first single
quote is treated as a text prefix character and isn't treated as part of the
cell's value. To get an initial single quote, you need to type two single
quotes at the beginning of a text constant.

 

[Harlan Grove]

it works in same workbook, however, when we put in path for a
different workbook, it doesn't work.
any idea why is that?

INDIRECT only works with open workbooks. See

http://groups.google.com/[email protected]