Hi, I have a group of files that are being updated separately, but all
reside in the same folder. There is 1 consolidated file in that same folder.
I need to create an update every week for the same set of files, which I do
by copying the entire folder to a new folder and update the files.

Is there a way that I can have the consolidation file pull from the files in
the folder that the consolidation file is in? I.e. I would prefer not having
to go in that file and manually change all the links.

thanks

Henk wrote...
Hi, I have a group of files that are being updated separately, but all
reside in the same folder. There is 1 consolidated file in that same folder.
I need to create an update every week for the same set of files, which I do
by copying the entire folder to a new folder and update the files.

Is there a way that I can have the consolidation file pull from the files in
the folder that the consolidation file is in? I.e. I would prefer not having
to go in that file and manually change all the links.

This shouldn't be a problem.

I just created files named 1.xls, 2.xls and summary.xls in folder D:\a.
I entered "a1" in [1.xls]Sheet1!A1 and "a2" in [2.xls]Sheet1!A1, and
the following formulas in [summary.xls]Sheet1.

B2:
=[1.xls]Sheet1!A1

B3:
=[2.xls]Sheet1!A1

I saved all 3 files and closed them. Then I copied all 3 of these
workbooks to the folder D:\b. I opened 1.xls and 2.xls changing their
respective Sheet1!A1 cells to "b1" and "b2". I saved the two files and
closed them. Then I opened D:\b\summary.xls, updated its links when it
opened, and Sheet1!B2:B3 displayed "b1" and "b2" rather than "a1" and
"a2".

IOW, Excel does seem to change links automatically under the
circumstances you mention - all files in the same folder.

Maybe this differs by Excel version, but this is how it seems to work
in Excel 2002.

Thanks Harlan,

This does work! thanks so much for your help. I tried something similar,
but couldn't make it work for some reason...

THX

"Harlan Grove" wrote:

Henk wrote...
Hi, I have a group of files that are being updated separately, but all
reside in the same folder. There is 1 consolidated file in that same folder.
I need to create an update every week for the same set of files, which I do
by copying the entire folder to a new folder and update the files.

Is there a way that I can have the consolidation file pull from the files in
the folder that the consolidation file is in? I.e. I would prefer not having
to go in that file and manually change all the links.

This shouldn't be a problem.

I just created files named 1.xls, 2.xls and summary.xls in folder D:\a.
I entered "a1" in [1.xls]Sheet1!A1 and "a2" in [2.xls]Sheet1!A1, and
the following formulas in [summary.xls]Sheet1.

B2:
=[1.xls]Sheet1!A1

B3:
=[2.xls]Sheet1!A1

I saved all 3 files and closed them. Then I copied all 3 of these
workbooks to the folder D:\b. I opened 1.xls and 2.xls changing their
respective Sheet1!A1 cells to "b1" and "b2". I saved the two files and
closed them. Then I opened D:\b\summary.xls, updated its links when it
opened, and Sheet1!B2:B3 displayed "b1" and "b2" rather than "a1" and
"a2".

IOW, Excel does seem to change links automatically under the
circumstances you mention - all files in the same folder.

Maybe this differs by Excel version, but this is how it seems to work
in Excel 2002.