Broken external references?

[Will Matthews]

Recently, we've experienced a new, and very frustrating
problem with Excel (the same problem occurs regardless of
using Excel v.X or Excel 2004).

We have a very large pricing spreadsheet that contains
about 100 external links to individuals files. As usual,
if we have the central file open as well as one of the
secondary files, the corresponding external reference
appears to be relative:

e.g.

=+'[BHH.xls]Leader'!P$506

When the linked file is closed, the reference becomes absolute:

e.g.

=+'Publicricing Spreadsheets:[BHH.xls]Leader'!P$506

These files are stored on our central file server. When
someone wants to work on them, they copy them to their
computer and work on them locally. At this point, the
reference should change to match the new path on the local
computer. However, it only changes the root level or drive
name in the path. The rest of the path remains the same:

e.g.

It should read
=+'Macintosh HD:Users:willesktop:[BHH.xls]Leader'!P$506

but instead reads
=+'Macintosh HDricing Spreadsheets:[BHH.xls]Leader'!P$506

This of course breaks the link as there is no such path on
the local computer's drive. Any ideas how I can keep this
from happening? As I say, it happens regarldess of whether
the files are opened in Excel v.X or Excel 2004.

Any suggestions would be much appreciated as this problem
is really forcing us to question the data integrity of
Excel in general.

Will