Linking Workbooks

[brumby]

hiya,

Please try to picture what I trying to describe. I think it makes sense.......

I have a master folder which each week I copy and paste into the current week eg :-

Master 2006 [copy][rename] = wk37 2006

Within that folder are a number of files / folders which my production teams enter their data through the week.

Also within the now named "wk37 2006" I have a summary sheet which links into the various production files.

Each week when I copy paste and rename, I have to go into my summary sheet and do search / replace as the links refer back to the original Master 2006 folder.

Is there a way to set the linking up so my summary sheet looks at the the wk number it is located in and not the original Master File?

Many Thanks,

 

[Andrew Poulsom]

I just tried a small experiment (Excel 2000).

I created 2 workbooks name Summary.xls and Data.xls and saved them in a folder Master 2006. In A1 on Sheet1 in Summary.xls I put the formula:

='P:\TEMP\Master 2006\[Data.xls]Sheet1'!$A$1

In Windows Explorer I created a new folder under P:\TEMP named wk37 2006. I copied the 2 workbooks from Master 2006 into that folder.

In Excel I opened P:TEMP\wk37 2006\Summary.xls. The formula in A1 was:

='P:\TEMP\wk37 2006\[Data.xls]Sheet1'!$A$1

So the link adjusted itself for the relative part of the path, without my having to find/replace.

Is your folder structure different from mine?

 

[GlennUK]

Where are the files that the Master File refers to?

 

[Richard Schollar]

Should you find yourself needing an alternative for changing links, then Bill Manville's Link Manager is pretty funky.

Richard

 

[brumby]

Strange......I dont think they are different :-

Master 2006 is in J:\Materials\Master2006 and has the following folders and files :-

Summary\ProductionSummary.xls
Line1\Line1.xls
Line2\Line2.xls
Line3\Line3.xls

When I copy and Paste the Master 2006 Folder it creates Copy Of Master 2006, which I rename to CW37 and creates :-

Summary\ProductionSummary.xls ( Still Linked to J:\Materials\Master2006\Line 1 2 & 3.xls.

Line1\Line1.xls
Line2\Line2.xls
Line3\Line3.xls

Does this help?

 

[Andrew Poulsom]

Try setting up the folder structure like this:

J:\Materials\Master2006\ProductionSummary.xls
J:\Materials\Master2006\Line1.xls
J:\Materials\Master2006\Line2.xls
J:\Materials\Master2006\Line3.xls