Using Cell contents as part of lookup

kanga

New Member
Joined
Oct 8, 2002
Messages
12
Hi All,

Can anyone tell me how to use the text content of one cell as a variable part of a lookup.

I have this external lookup
'[Stock Prices.xls]STO'!$M$3
with "STO" a variable stock code as text in a cell as well as the name of a sheet in Stock Prices.xls

What I would like to do is have a variable in the lookup rather than "STO"

Any ideas ??

Thanks

Peter
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
On 2002-11-05 09:14, kanga wrote:
Hi All,

Can anyone tell me how to use the text content of one cell as a variable part of a lookup.

I have this external lookup
'[Stock Prices.xls]STO'!$M$3
with "STO" a variable stock code as text in a cell as well as the name of a sheet in Stock Prices.xls

What I would like to do is have a variable in the lookup rather than "STO"

Any ideas ??

Thanks

Peter


I think you're on the lines of INDIRECT?

Say A1 contains STO

=VLOOKUP(A1,INDIRECT("'[Stock Prices.xls]" & A1 & "'!A:B"),2,FALSE)

Does that do the trick?
 
Upvote 0
Hi,

INDIRECT might be the go but I cant get it to work successfully.

My lookup is not even as fancy as a vlookup. It just includes a path and an absolute reference.

='C:DataSharesPrice Data[Stock Prices.xls]NAB'!$M$4
where "NAB" is the stock code (say cell C2)as well as the external sheet name.

I tried replacing NAB in the above with
Indirect(C2) in various combinations but just get an error. I also tried using the whole reference as an argument of indirect but also no joy.

Any more ideas?

Thanks,

Peter
 
Upvote 0
On 2002-11-06 03:32, kanga wrote:
Hi,

INDIRECT might be the go but I cant get it to work successfully.

My lookup is not even as fancy as a vlookup. It just includes a path and an absolute reference.

='C:DataSharesPrice Data[Stock Prices.xls]NAB'!$M$4
where "NAB" is the stock code (say cell C2)as well as the external sheet name.

I tried replacing NAB in the above with
Indirect(C2) in various combinations but just get an error. I also tried using the whole reference as an argument of indirect but also no joy.

Any more ideas?

Thanks,

Peter

Try

with NAB in C2

=INDIRECT("'C:DataSharesPrice Data[Stock Prices.xls]" & C2 & "'!$M$4")

does that work?
 
Upvote 0
Hi Luke,

No joy. I still just get the #Ref! error.

Interestingly the quote on the reply leaves out the "" from the path which made it look a bit weird. I copied and pasted the file name to make sure there were no errors but same result.

It's not a big deal and I can just find/replace the text without any problem.

Thanks anyway - I'll play with Indirect a bit more.

Good luck with the test.
 
Upvote 0
Not sure if this is the board playing up again but it should read:

C:....

not C:blah

you need the
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top