How do I use vlookup to point to an external file that changes nam
 

I want my file to use vlookup and pull data from the current worksheet but I
also want to copy the formula to subsequent columns which represent dates
(week ending... ie. 010705, 011405, 012105, etc.) throughout eternity without
having to manually update the formula. The File names 010705... will be in a
centralized directory and contain data pertaining to that week and I want it
to be returned to the master spreadsheet. When I copy the vlookup formula I
want it to automatically change the file it looks in to correspond with
pertinenent weekly data.

Thanks for your help.

Aschaney wrote...
I want my file to use vlookup and pull data from the current worksheet
but I
also want to copy the formula to subsequent columns which represent
dates
(week ending... ie. 010705, 011405, 012105, etc.) throughout eternity
without
having to manually update the formula. The File names 010705... will
be in a
centralized directory and contain data pertaining to that week and I
want it
to be returned to the master spreadsheet. When I copy the vlookup
formula I
want it to automatically change the file it looks in to correspond
with
pertinenent weekly data.

If once entered these formulas wouldn't change, then a 3-step process
will be the quickest way to do this.

1. Create text formulas that will produce results that will look like
the formulas you want. If the 7-Jan-2005 formula would be in cell C5,
something like

C5:
="=VLOOKUP(X99,'[foobar.xls]"&TEXT(DATE(2005,1,7)+7*(COLUMN()-3),"MMDDYY")
&"'!C3:J1002,6,0)"

Fill right as needed.

2. Select all these cells, copy, and paste special as values on top of
them to change these formulas to text constants.

3. With these cells still selected, Edit Replace, replacing all =
with =. This may seem like a do-nothing operation, but it has the
effect of re-entering all these cells, which thus enters them as
formulas.

We have considered using the replace function as a last option as there are
infinite weeks in time and we do not want to have to replace the lookup
"table" value in each formula. We would have the file name statically placed
in the column above the function and that is the file name we want the
vlookup to reference when copied, even if it is a new file name in the column
to the right.

"Harlan Grove" wrote:

Aschaney wrote...
I want my file to use vlookup and pull data from the current worksheet
but I
also want to copy the formula to subsequent columns which represent
dates
(week ending... ie. 010705, 011405, 012105, etc.) throughout eternity
without
having to manually update the formula. The File names 010705... will
be in a
centralized directory and contain data pertaining to that week and I
want it
to be returned to the master spreadsheet. When I copy the vlookup
formula I
want it to automatically change the file it looks in to correspond
with
pertinenent weekly data.

If once entered these formulas wouldn't change, then a 3-step process
will be the quickest way to do this.

1. Create text formulas that will produce results that will look like
the formulas you want. If the 7-Jan-2005 formula would be in cell C5,
something like

C5:
="=VLOOKUP(X99,'[foobar.xls]"&TEXT(DATE(2005,1,7)+7*(COLUMN()-3),"MMDDYY")
&"'!C3:J1002,6,0)"

Fill right as needed.

2. Select all these cells, copy, and paste special as values on top of
them to change these formulas to text constants.

3. With these cells still selected, Edit Replace, replacing all =
with =. This may seem like a do-nothing operation, but it has the
effect of re-entering all these cells, which thus enters them as
formulas.

Aschaney wrote...
We have considered using the replace function as a last option as
there are
infinite weeks in time and we do not want to have to replace the
lookup
"table" value in each formula. We would have the file name statically
placed
in the column above the function and that is the file name we want the

vlookup to reference when copied, even if it is a new file name in the
column
to the right.
....

There aren't an infinite number of columns or rows in Excel worksheets,
so you're obliged to work within constraints anyway. Also, if you're
dealing in chunks of weeks, it's quite likely you'll be using something
other than a spreadsheet in 10-20 years anyway.

Be that as it may, you're now mentioning 'file name' when you had
mentioned 'worksheet' in your original posting. These terms have
precise meanings, so you need to try to use them correctly.

workbook = file
worksheet is one 'tab' within a workbook or file

I'll assume you do mean workbook filename, and that you misused the
term 'worksheet' previously.

You're also misusing the term 'column'. I can understand 'column to the
right', but not 'in the column above'. Do you mean 'in the row above'?
Do you have filenames in both the row above and the column to the right
of the cell in which you're trying to enter the VLOOKUP formula? If so,
which are you trying to use?

If you have different static workbook filenames in some range of cells,
and you want a corresponding range of VLOOKUP calls using those
filenames in external references, and *IF* those workbooks would *ALL*
be open in the same Excel session, then you could use the INDIRECT
function.

=VLOOKUP(whatever,
INDIRECT("'["&ReferenceToCellContainingFilename&"]Some
Worksheet'!X99:Y500"),
2,0)

If your files won't all be open, see option 4 in the following linked,
archived message.

http://groups-beta.google.com/group/...443753560f0075
(or http://makeashorterlink.com/?F2993260A ).