Making a file and worksheet reference into a variable....

[Guest]

I have this formula in cell B1.
=VLOOKUP(A1,[Book1]Sheet1!$A:$M,2,FALSE)

In cell A2, I have a file name/location
In cell A3, I have a worksheet name

I would like to alter this formula so that instead of [book1], it can point
to cell A2 and look for whatever file I type in there. The same with the
worksheet name Sheet1, I'd like it to point to A3 so I can type that in too.

I would do this the standard way, however due to the number of vlookups on
this spreadsheet, I need to be able to type the file name and worksheet name
into these cells, and have all the vlookups adjust accordingly. I know
find/replace does work for that, but only in a permament formula. I need
something variable.

Thanks for your help!

 

[Bernard Liengme]

Something to get you started:
a) with Sheet2 as text in B3 use =VLOOKUP(A1,INDIRECT(B3&"!A10:B13"),2) to
do a look up of a table on Sheet2
or
b) with the value 2 in B5 use =VLOOKUP(A1,INDIRECT("Sheet"&B5&"!A10:B13"),2)
to do a look up on Sheet2.

Expand on this to incorporate filename.
I think the file must be open for lookup functions to work

best wishes

 

[Bob Phillips]

Expand on this to incorporate filename.
I think the file must be open for lookup functions to work

For INDIRECT Bernard....

 

[Guest]

Worked great! Thank you for introducing me to that function.

Something to get you started:
a) with Sheet2 as text in B3 use =VLOOKUP(A1,INDIRECT(B3&"!A10:B13"),2) to
do a look up of a table on Sheet2
or
b) with the value 2 in B5 use =VLOOKUP(A1,INDIRECT("Sheet"&B5&"!A10:B13"),2)
to do a look up on Sheet2.

Expand on this to incorporate filename.
I think the file must be open for lookup functions to work

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

I have this formula in cell B1.
=VLOOKUP(A1,[Book1]Sheet1!$A:$M,2,FALSE)

In cell A2, I have a file name/location
In cell A3, I have a worksheet name

I would like to alter this formula so that instead of [book1], it can
point
to cell A2 and look for whatever file I type in there. The same with the
worksheet name Sheet1, I'd like it to point to A3 so I can type that in
too.

I would do this the standard way, however due to the number of vlookups on
this spreadsheet, I need to be able to type the file name and worksheet
name
into these cells, and have all the vlookups adjust accordingly. I know
find/replace does work for that, but only in a permament formula. I need
something variable.

Thanks for your help!