software

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\amp;INDIRECT(lt;Cell ref for John
Smithgt;)amp;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.

quot;ERR229quot; wrote:

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

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

quot;kassiequot; wrote:

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