Macro to replace file name many times
 

I have a formula which calls data from another spreadsheet:

='[Vac 0309.xls]Sheet1'!$D$2

The formula is repeated many times starting at cell B1. Is there a macro
which will replace the file name with the file name in cell A1 then A2, A3
etc. until there are no more files names listed in column A. The purpose is
to pull data from about 200 separate spreadsheets. The data is always in the
same sheet and cell.

Macro to replace file name many times
 

See this page
http://www.rondebruin.nl/summary2.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"msdrolf" wrote in message ...
I have a formula which calls data from another spreadsheet:

='[Vac 0309.xls]Sheet1'!$D$2

The formula is repeated many times starting at cell B1. Is there a macro
which will replace the file name with the file name in cell A1 then A2, A3
etc. until there are no more files names listed in column A. The purpose is
to pull data from about 200 separate spreadsheets. The data is always in the
same sheet and cell.

Macro to replace file name many times
 

No need for a macro; simple formula works
With A1 having the text: Vac 0309
The formula =INDIRECT("'["&A1&".XLS]Sheet1'!E9") will work in B1
But if you are copying it then use
=INDIRECT("'["&A1&".XLS]Sheet1'!$E$1")

Note after the open parenthesis we have <double-quote<single-quote

best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"msdrolf" wrote in message
...
I have a formula which calls data from another spreadsheet:

='[Vac 0309.xls]Sheet1'!$D$2

The formula is repeated many times starting at cell B1. Is there a macro
which will replace the file name with the file name in cell A1 then A2, A3
etc. until there are no more files names listed in column A. The purpose
is
to pull data from about 200 separate spreadsheets. The data is always in
the
same sheet and cell.

Macro to replace file name many times
 

etc. until there are no more files names listed in column A
Sorry I not read your question good

But maybe the macro is useful

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
See this page
http://www.rondebruin.nl/summary2.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"msdrolf" wrote in message ...
I have a formula which calls data from another spreadsheet:

='[Vac 0309.xls]Sheet1'!$D$2

The formula is repeated many times starting at cell B1. Is there a macro
which will replace the file name with the file name in cell A1 then A2, A3
etc. until there are no more files names listed in column A. The purpose is
to pull data from about 200 separate spreadsheets. The data is always in the
same sheet and cell.

Macro to replace file name many times
 

Ron,
Thanks for the link. My macro knowledge is still developing but it looks
like your macro will do what I want to do and then some.

Macro to replace file name many times
 

IT WORKS! Many thanks, Bernard

One amendment. I found that when I saved/closed the input worksheet the
formula returned an error. However, if the path is entered between the
apostrophe and the first [ bracket then it works even after closing the input
worksheet.

Rolf