filtering vlookup formulas in another directory

[jroo]

Hi,

I have a worksheet set up with 100 records containing the following information.

Column A contains ID codes, which are also the filenames of excel files in the "my documents" directory.

Column B contains the vlookup reference

Column C contains the vlookup formula

example:

A|B|C
ID|Line|Vlookup

123456|8100|=VLOOKUP(B1,'C:\[123456.xls]Sheet1'!$A$1:$B$100,2,FALSE)

654321|8300|=VLOOKUP(B2,'C:\[654321.xls]Sheet1'!$A$1:$B$100,2,FALSE)

111666|8295|=VLOOKUP(B3,'C:\[111666.xls]Sheet1'!$A$1:$B$100,2,FALSE)

....etc

so for the remaining rows i'd like to autofill the vlookup formulas. But I'm having trouble. When I autofill he vlookup reference changes as I want it to (i.e. B4, B5, B6...), HOWEVER, the excel file reference [111666.xls] doesn't change. How do I insert the value in Columns A into "[ .xls]. Any ideas??

I was trying to be clever and so something like ="["&A1&"].xls" and filtering down...but that didn't work

Possibly using vba and doing a find edit replace might be possible as well

 

[Corni]

You can use this code:

For i=1 to 100
Cells(i,3).Formula="=VLOOKUP(B" & i & ",'C:\[" & Cells(1,1) & _
".xls]Sheet1'!$A$1:$B$100,2,FALSE)"
Next i

 

[jroo]

Thanks!