Referencing a filename in a formula

I have a file containing a list of names in column A and a lookup to
individual data files in column B

John Smith =Vlookup(A1,F:\Data\[John Smith]Sheet1!$A$1:$D$50,3,false)
Jane Doe =Vlookup(A1,F:\Data\[Jane Doe]Sheet1!$A$1:$D$50,3,false)

The players sometimes change, so the reference to the file changes. I would
like to be able to pull the filename reference in the formula from the name
in column A. So if I change John Smith to Ted Nugent, the formula
automatically references Ted Nugent's file.

Does anyone know if this can be done?

Thanks in advance for your help!

--ERR

--
ERR229

Try using INDIRECT. =VLOOKUP(A1,F:\Data\&INDIRECT(<Cell ref for John
Smith>)&Sheet1!$A$1:$D$50,3,FALSE).

I don't have another drive to test this, but it might work. I tested with
range names on the same workbook, and it worked fine.

"ERR229" wrote:

> I have a file containing a list of names in column A and a lookup to
> individual data files in column B
>
> John Smith =Vlookup(A1,F:\Data\[John Smith]Sheet1!$A$1:$D$50,3,false)
> Jane Doe =Vlookup(A1,F:\Data\[Jane Doe]Sheet1!$A$1:$D$50,3,false)
>
> The players sometimes change, so the reference to the file changes. I would
> like to be able to pull the filename reference in the formula from the name
> in column A. So if I change John Smith to Ted Nugent, the formula
> automatically references Ted Nugent's file.
>
> Does anyone know if this can be done?
>
> Thanks in advance for your help!
>
> --ERR
>
> --
> ERR229

If the other files are closed then you will need to use INDIRECT.EXT (see
http://xcell05.free.fr/english/)

"kassie" wrote:

> Try using INDIRECT. =VLOOKUP(A1,F:\Data\&INDIRECT(<Cell ref for John
> Smith>)&Sheet1!$A$1:$D$50,3,FALSE).
>
> I don't have another drive to test this, but it might work. I tested with
> range names on the same workbook, and it worked fine.
>
> "ERR229" wrote:
>
> > I have a file containing a list of names in column A and a lookup to
> > individual data files in column B
> >
> > John Smith =Vlookup(A1,F:\Data\[John Smith]Sheet1!$A$1:$D$50,3,false)
> > Jane Doe =Vlookup(A1,F:\Data\[Jane Doe]Sheet1!$A$1:$D$50,3,false)
> >
> > The players sometimes change, so the reference to the file changes. I would
> > like to be able to pull the filename reference in the formula from the name
> > in column A. So if I change John Smith to Ted Nugent, the formula
> > automatically references Ted Nugent's file.
> >
> > Does anyone know if this can be done?
> >
> > Thanks in advance for your help!
> >
> > --ERR
> >
> > --
> > ERR229