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 ).