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

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

msdrolf wrote:
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

neat answer
--
Allllen


"John Coleman" wrote:

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

msdrolf wrote:
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

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.

"John Coleman" wrote:

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

msdrolf wrote:
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

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

msdrolf wrote:
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.

"John Coleman" wrote:

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

msdrolf wrote:
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

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


"msdrolf" wrote:

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