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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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?
 

kanga

New Member
Joined
Oct 8, 2002
Messages
12
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
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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?
 

kanga

New Member
Joined
Oct 8, 2002
Messages
12

ADVERTISEMENT

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.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Not sure if this is the board playing up again but it should read:

C:....

not C:blah

you need the
 

Forum statistics

Threads
1,143,707
Messages
5,720,386
Members
422,282
Latest member
psunith

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
Top