If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
variable reference in hlookup array
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 |
#2
|
|||
|
|||
variable reference in hlookup array
One way
=HLOOKUP(6,INDIRECT("'"&A4&"'!D2:Z50"),9) -- Regards, Peo Sjoblom wrote in message ... 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 |
#3
|
|||
|
|||
variable reference in hlookup array
why =HLOOKUP(6,INDIRECT(A4 & "D2:Z50"),9), A4='9000'! doesn't work?
"Peo Sjoblom" wrote in message ... One way =HLOOKUP(6,INDIRECT("'"&A4&"'!D2:Z50"),9) -- Regards, Peo Sjoblom wrote in message ... 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 |
#4
|
|||
|
|||
variable reference in hlookup array
it works in same workbook, however, when we put in path for a different
workbook, it doesn't work. any idea why is that? One way =HLOOKUP(6,INDIRECT("'"&A4&"'!D2:Z50"),9) -- Regards, Peo Sjoblom wrote in message ... 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 |
#5
|
|||
|
|||
variable reference in hlookup array
"Soe" soe@soe wrote...
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. |
Thread Tools | |
Display Modes | |
|
|