variable reference in hlookup array

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
it works in same workbook, however, when we put in path for a different
workbook, it doesn't work.
any idea why is that?
 
Soe said:
why =HLOOKUP(6,INDIRECT(A4 & "D2:Z50"),9), A4='9000'! doesn't work?
Click to expand...
....

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.
 
Back
Top