Maintain Relative Link to Workbook in Parent Directory

Hey Everyone,

I have many different spreadsheets with cells that are linked together. Most of the files which are referenced in the links are in the same directory or a child directory. However, several are located in parent directories (above the current directory).

My goal is to keep the cell links intact if all the files are moved to a different directory while maintaining the same RELATIVE heirarchy. Thus, I want the files in the new location to depend on the files related to that new position and NOT the old locations of the files.

When I transfer all the files, the links that refer to spreadsheets in the current directory or child directories are all maintained properly. The problem is that any file referencing a workbook in a folder above it (parent directory) becomes an ABSOLUTE reference to the spreadsheet in the original location.

Is there some way to accomplish this task without getting into VBA? I would prefer to keep the worksheets transparent (as in, so that someone can easily see links) and just have a ='C:\Test\child\linked.xlsx' style of reference in a cell. I tried using windows relative path methods, such as "C:..\source.xlsx' to refer to files in a parent directory, but couldn't get it to work.

I apologize if I missed this subject elsewhere. . . it seems rather simple. . . but I was having considerable trouble answering this question looking at this and other forums.

Thanks!

Two suggestions come to mind.

A workbook wide find and replace on the directory specific part of your reference.

Using the INDIRECT formula

Originally Posted by firefly2k8

Two suggestions come to mind.

A workbook wide find and replace on the directory specific part of your reference.

Using the INDIRECT formula

Thanks for your response firefly2k8,

I looked into both of your suggestions:

1.) I am trying to avoid find/replace so that if future network changes or another user changes the location of all the files (via moving a folder above it), then the references will still find the linked workbooks in the same relative location. I would prefer users don't have to re-attach the links later on or find/replace. I want to lock everything below a certain level when done so that my calculations stay intact.

2.) INDIRECT sounded promising until I read Microsoft Support description where it stated:

Note The INDIRECT function only returns the result of a reference to an open file. If a workbook that the INDIRECT function is indirectly referencing is closed, the function returns a #REF! error.

I would like the links to work even if the referenced books are not open.

Alright, say you have a file called linkedParent.xlsx. This file contains a link to a cell from a Source.xlsx located in the folder directly above the folder in which linkedParent.xlsx resides.

Suppose these files are all located on the C: such that the Source.xlsx is contained in C:\Test

The spreadsheet linkedParent.xlsx then resides in C:\Test\child
And a cell of linkedParent.xlsx contains the formula:

Please Login or Register  to view this content.

1.) Now, if linkedParent.xlsx is copied to a different folder (i.e. C:\Test\child\child2), the cell link will still point to the same location as it did originally.

2.) On the contrary, if the link were originally made to a Source.xlsx spreadsheet in a child folder instead of a parent folder, C:\Test\child\child2, then when the same copy-paste of the file occured, the link would point to a file in C:\Test\child\child2\child2.

In the first case the link is treated as absolute, and in the second it is relative. Is there some way that it could be made relative in the first case as well?

I have now also posted this subject at MrExcel Forums http://www.mrexcel.com/forum/showthread.php?t=624086

I will update both this thread and that one if/when this issue is resolved.