Macro to change file name in Vlookup formula

[msdrolf]

I have a vlookup formula in column B:
=VLOOKUP($A$10,[DATA2.xls]Sheet1!$A$1:$B$7,2,FALSE)
I have copied this 200 times to sequential rows in column B to pull data
from 200 other spreadsheets.
Is it possible to have a macro which would replace the file name DATA2.xls
with the file name listed in column A of the same row and continue to do so
until it reaches the end of the column.
Thanks

 

[John Coleman]

Maybe you don't need a macro. Say that the file names begin in row 1 of
column A. Then in B1 enter:

=VLOOKUP($A$10,INDIRECT(CONCATENATE("[",A1,"]Sheet1!$A$1:$B$7")),2,FALSE)

And copy it down 200 times.

HTH

-John Coleman

 

[Allllen]

try this.

Sub trythis()
i = 1
Do Until IsEmpty(Cells(i, 2))
Cells(i, 2).Formula = Replace(Cells(i, 2).Formula, "DATA2.xls", Cells(i,
1).Value)
i = i + 1
Loop
End Sub

 

[Allllen]

neat answer
--
Allllen

Maybe you don't need a macro. Say that the file names begin in row 1 of
column A. Then in B1 enter:

=VLOOKUP($A$10,INDIRECT(CONCATENATE("[",A1,"]Sheet1!$A$1:$B$7")),2,FALSE)

And copy it down 200 times.

HTH

-John Coleman

I have a vlookup formula in column B:
=VLOOKUP($A$10,[DATA2.xls]Sheet1!$A$1:$B$7,2,FALSE)
I have copied this 200 times to sequential rows in column B to pull data
from 200 other spreadsheets.
Is it possible to have a macro which would replace the file name DATA2.xls
with the file name listed in column A of the same row and continue to do so
until it reaches the end of the column.
Thanks

 

[msdrolf]

Thanks for your suggestion. Unfortunatley it has only limited application.
You need to keep all the source workbooks open. The INDIRECT connection is
broken when you close the source files. The VLOOKUP link remains even after
the files are closed. That is why I was hoping there was an easy way to
change the file name in the VLOOKUP formula. Thanks for trying.

Maybe you don't need a macro. Say that the file names begin in row 1 of
column A. Then in B1 enter:

=VLOOKUP($A$10,INDIRECT(CONCATENATE("[",A1,"]Sheet1!$A$1:$B$7")),2,FALSE)

And copy it down 200 times.

HTH

-John Coleman

I have a vlookup formula in column B:
=VLOOKUP($A$10,[DATA2.xls]Sheet1!$A$1:$B$7,2,FALSE)
I have copied this 200 times to sequential rows in column B to pull data
from 200 other spreadsheets.
Is it possible to have a macro which would replace the file name DATA2.xls
with the file name listed in column A of the same row and continue to do so
until it reaches the end of the column.
Thanks

 

[John Coleman]

Alllen's VBA approach should work with little problem in that case. I
find it disappointing that
Excel's worksheet reference functions are too weak to handle this
directly. I was hoping that some work around was possible.

-John Coleman

Thanks for your suggestion. Unfortunatley it has only limited application.
You need to keep all the source workbooks open. The INDIRECT connection is
broken when you close the source files. The VLOOKUP link remains even after
the files are closed. That is why I was hoping there was an easy way to
change the file name in the VLOOKUP formula. Thanks for trying.

Maybe you don't need a macro. Say that the file names begin in row 1 of
column A. Then in B1 enter:

=VLOOKUP($A$10,INDIRECT(CONCATENATE("[",A1,"]Sheet1!$A$1:$B$7")),2,FALSE)

And copy it down 200 times.

HTH

-John Coleman

I have a vlookup formula in column B:
=VLOOKUP($A$10,[DATA2.xls]Sheet1!$A$1:$B$7,2,FALSE)
I have copied this 200 times to sequential rows in column B to pull data
from 200 other spreadsheets.
Is it possible to have a macro which would replace the file name DATA2.xls
with the file name listed in column A of the same row and continue to do so
until it reaches the end of the column.
Thanks