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!
> >
> >
>
>
>