Vlookup question

[ingleg]

Hi

I am compiling a spreadsheet that is a series of vlookup columns.

My formula is similar to:

=VLOOKUP(A1,'C:\[Test31032006.xls]Sheet1'!$A:$B,2,0)

Where 31032006 in the file name is the date looked at.

Is it possible to change the formula, to look at a cell reference, and
make it look at the correct file, eg Test01042006.xls?

Thanks in advance.

 

[Dave O]

The simplest method may be to search for 31032006 and replace with
01042006: on the menu click >Edit >Replace and fill in the boxes as
appropriate.

 

[ingleg]

I started doing that, but with one column per day and 200 rows, it take
quite a while to do.

I tried repeating the steps in a macro to save effort, but it stil
took a long time.

I have several spreadsheets like this to do and dont want to spend s
long doing each one

 

[Bob Phillips]

Not really, as I assume that Test01042006 will be closed, and the usual
method uses INDIRECT, which does not work with closed workbooks. In this
case you would use

=VLOOKUP(A1,INDIRECT("'C:\["&A1&"]Sheet1'!$A:$B",2,0)

but there is a solution at
http://makeashorterlink.com/?F2993260A


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)