A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

variable reference in hlookup array



 
 
Thread Tools Display Modes
  #1  
Old May 7th, 2004, 11:45 PM
external usenet poster
 
Posts: n/a
Default 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  
Old May 7th, 2004, 11:55 PM
Peo Sjoblom
external usenet poster
 
Posts: n/a
Default 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  
Old May 10th, 2004, 06:01 AM
Soe
external usenet poster
 
Posts: n/a
Default 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  
Old May 10th, 2004, 07:08 AM
Soe
external usenet poster
 
Posts: n/a
Default 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  
Old May 10th, 2004, 10:13 AM
Harlan Grove
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:04 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.