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

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!

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

"Mr Mike" <MrMike@discussions.microsoft.com> wrote in message
news:E21F473F-E96D-473A-8F7A-A4991573F740@microsoft.com...
>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" <bliengme@stfx.TRUENORTH.ca> wrote in message
news:e69kyulgFHA.2424@TK2MSFTNGP09.phx.gbl...
>
> Expand on this to incorporate filename.
> I think the file must be open for lookup functions to work

For INDIRECT Bernard....

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

"Bernard Liengme" wrote:

> 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
>
> "Mr Mike" <MrMike@discussions.microsoft.com> wrote in message
> news:E21F473F-E96D-473A-8F7A-A4991573F740@microsoft.com...
> >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!
> >
> >
>
>
>