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.