Changing External References

[OhJackie]

I have a problem that involves updating external
references. I have worksheets that are organized by week
and reference sheets from 3 folders. Here is a picture.

Week1
Folder1 Folder2 Folder3
sheet1 sheet4 sheet7
sheet2 sheet5 sheet8
sheet3 sheet6 sheet9

Sheets 7-8 present data from the worksheets in folders 1
and 2. At the start of a new week the week folder is
copied and renamed. Because the pathnames for external
references are absolute, each worksheet has to be opened
and the links updated by browsing to the new weeks source
files.

Is there a quick way to change external reference links or
a way to specify relative pathnames in formulas
(like ..\Folder2)?

 

[Bob Phillips]

Have you tried Find & Replace (Ctrl-H)?

 

[Arvi Laanemets]

Hi

It's not too clear what exactly you want to do, but anyway some possible
ways to possible solutions.

1. Keep current week's data always in same folder(s). When the week is over,
then copy and rename folders with p.e. week number in name. This way you
don't need to edit the links in current weeks folsers at all, (but of-course
you'll have problems with archived workbooks, except you replace all links
with values).

2. Create new (empty) folders. Open all workbooks in Excel, and copy them
all to new destinations, using SaveAs dialog. All links are adjusted
automatically. You can use this method to both ways - to create new
workbooks for new week, or to archive past week data in according archive
folders.

3. As was adviced by Bob, use Replace feature. You have to do it for every
worksheet, which contains links, in every workbook - looks like a lot of
work

 

[Arvi Laanemets]

An important addition to p.2
.... Open all workbooks in Excel, and AFTER THAT copy them all to new
destinations, using SaveAs dialog (Don't close any workbook until all of
them are renamed). ...