Is there a way to do relative links in Excel?

[Jennifer]

I am creating monthly summary files that grab information from many other
files. Prior to this year, the information has been linked using complete
path information.

We are currently changing our network, so old paths will no longer be valid.
We are trying to work out a directory structure which best meets not only
our current needs, but also allows for portability (in case the network is
changed again, or the files need to be moved).

What I would like to be able to do is to have the links all be relatve
rather than absolute file paths.

The following is an example of our directory structure:
- the main file is in h:\lmt\Statistics\2006\January
- some supporting (source) documents are in this same directory
(h:\lmt\Statistics\2006\January)
- other supporting (source) documents are in a sub-directory
(h:\lmt\Statistics\2006\January\from_Systems)
- There may also be cases (as with summaries or year-to-year comparisons),
where I need to go "up and over" (h:\lmt\Statistics\2005...)

Instead of having a formula that reads
='H:\lmt\Statistics\2006\January\[Jan06-BRI.xls]DAILYSTATS'!$B$5

I would like to have a relative formula, but I don't know what it would look
like. Maybe something like this:
='\[Jan06-BRI.xls]DAILYSTATS'!$b$5' ???

I would really appreciate it if someone could help me with this.

Sincerely,

Jennifer

[JMB]

Edit/Links/Change Source


"Jennifer" wrote:

> I am creating monthly summary files that grab information from many other
> files. Prior to this year, the information has been linked using complete
> path information.
>
> We are currently changing our network, so old paths will no longer be valid.
> We are trying to work out a directory structure which best meets not only
> our current needs, but also allows for portability (in case the network is
> changed again, or the files need to be moved).
>
> What I would like to be able to do is to have the links all be relatve
> rather than absolute file paths.
>
> The following is an example of our directory structure:
> - the main file is in h:\lmt\Statistics\2006\January
> - some supporting (source) documents are in this same directory
> (h:\lmt\Statistics\2006\January)
> - other supporting (source) documents are in a sub-directory
> (h:\lmt\Statistics\2006\January\from_Systems)
> - There may also be cases (as with summaries or year-to-year comparisons),
> where I need to go "up and over" (h:\lmt\Statistics\2005...)
>
> Instead of having a formula that reads
> ='H:\lmt\Statistics\2006\January\[Jan06-BRI.xls]DAILYSTATS'!$B$5
>
> I would like to have a relative formula, but I don't know what it would look
> like. Maybe something like this:
> ='\[Jan06-BRI.xls]DAILYSTATS'!$b$5' ???
>
> I would really appreciate it if someone could help me with this.
>
> Sincerely,
>
> Jennifer
>
>
>