Get relative Path: advanced workbook linking.

  • Thread starter Thread starter dakke
  • Start date Start date
D

dakke

This might be a little off topic, but I'll ask anyway.

I work on multiple workbooks. They all calculate a lot of functions and I am
forced to create multiple workbooks to do these calculations. Linking to
other workbooks basically displays the results of workbook1 and works from
there.

But...

I need to keep an overview of these files, so I store them in different
folders all within the same 'master' folder. So let's say: master folder is
FolderMaster, and this contains folderA, folderB, folderC. FolderA contains
workbookA1, workbookA2; folderB contains workbookB1, workbookC2 and finally
folderC contains workbookC1 and workbookC2.

Suppose that this hierarchy might change. Moving eg workbook A2 to folderB.
This would cause a #REF.

So I thought: organise your hierarchy in a 'master workbook' and store all
paths to the individual files in a worksheet. If I link to a specific folder
I do not directly link to the actual folder, but would use INDIRECT and grap
the filepath from the masterworkbook. If the path to a workbook change I do
not have to update all individual links, but only change the path in the
masterworkbook. Right?

But how is the big one here. How do you formulate you path to a file? It can
not be the entire file path since that's variable... (sometimes
'hd:User1:....'; sometimes 'HD:User2'...")
 
Hi dakke,

If you're going to make a hierarchy change, open both the source and target workbooks (with the source workbook in the old folder),
then use File|Save As to save to source workbook to the new folder. That will update the paths in the target workbook. Then delete
(or move) the old source workbook.

Cheers
 
Back
Top