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.