If you are a Small Business customer, find additional troubleshooting and learning resources at the Support for Small Business site.

In Microsoft Excel, you can link a cell in a workbook to another workbook using a formula that references the external workbook. When this link is created, it may use a relative path. With a relative link, you can move the workbooks without breaking the link.

This article discusses how the
references to linked workbooks are stored by Excel in different circumstances.

How link paths are handled when a file opens

When Excel opens a file that contains links (linked workbook), it
combines the portions of the links stored in the file with the necessary
portions of the current path of the linked workbook.

How link paths are stored

When Excel stores the path to a linked file, it uses the
following rules to determine what to store.

Note Moving up a path indicates that you are referring to folders
moving away from the root drive or share. Moving down a path indicates that you
are moving closer to the root drive or share.

If the linked file and the source data file are not on the
same drive, the drive letter is stored with a path to the file and file
name.

If the linked file and the source data file are in the same
folder, only the file name is stored.

If the source data file is located in a folder that is nested in the same root folder as the linked file, a property is stored to indicate the root folder. All portions of the path that are shared are not stored.

For example, if the linked file C:\Mydir\Linked.xls is dependent on C:\Mydir\Files\Source.xls, the only portion of the path that is stored is \Files\Source.xls.

If the source data file is one folder down from the linked
file, a property is stored to indicate this.

For example, the linked
file is C:\Mydir\Files\Myfile\Linked.xls and the source data file is
C:\Mydir\Files\Source.xls. Excel stores only \MyDir\Files\ .. \Source.xls.

Note This allows a link to be maintained when the linked file is
copied to an additional sub folder of the folder that the source file is
located in.

For example, the linked file is
C:\Mydir\Files\Myfiles1\Linked.xls and the source data file is
C:\Mydir\Files\Source.xls, the linked file, Linked.xls, is copied from the
folder C:\Mydir\Files\Myfiles1 to a folder named C:\Mydir\Files\Myfiles2, and
the link to C:\Mydir\Files\Source.xls is maintained.

If the source data file is located in the XLStart, Alternate Startup File Location, or the Library folder, a property is written
to indicate one of these folders, and only the file name is stored.

Note Excel recognizes two default XLStart folders from which to automatically open files on startup. The two folders are as follows:

The XLStart folder that is in the Office installation folder, such as C:\Program Files\Microsoft Office\Office folder\XLStart

The XLStart folder that is in the user's profile, such as C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLStart

The XLStart folder that is in the user's profile is the XLStart folder that will be stored as a property for the link. If you use the XLStart folder that is in the Office installation folder, that XLStart folder is treated like any other folder on the hard disk.

The Office folder name changes between versions of Office. For example, the Office folder name is Office, Office10, Office11 or Office12, depending on the version of Office that you are running. This folder name change causes links to be broken if you move to a computer that is running a different version of Excel than the version in which the link was established.

It is also important to note that what appears in the formula
bar is not necessarily what is stored. For example, if the source data file is
closed, you see a full path to the file, although only the file name may be
stored.

Relative vs. absolute links

Links to external workbooks are created in a relative manner
whenever possible. This means that the full path to the source data file is not
recorded, but rather the portion of the path as it relates to the linked
workbook. With this method, you can move the workbooks without breaking the
links between them. The links remain intact, however, only if the workbooks
remain in the same location relative to each other. For example, if the linked
file is C:\Mydir\Linked.xls and the source data file is
C:\Mydir\Files\Source.xls, you can move the files to the D drive as long as the
source file is still located in a subfolder called "files".

Relative links may cause problems if you move the linked file to different computers and the source is in a central location.

Mapped drives vs. UNC

When a source data workbook is linked, the link is established
based on the way that the workbook was opened. If the workbook was opened over
a mapped drive, the link is created by using a mapped drive. The link remains
that way regardless of how the source data workbook is opened in the future. If
the source data file is opened by a UNC path, the link does not revert to a
mapped drive, even if a matching drive is available. If you have both UNC
and mapped drive links in the same file, and the source files are open at the
same time as the destination file, only those links that match the way the file
was opened will react as hyperlink. Specifically, if you open the file through
a mapped drive and change the values in the source file, only those links
created to the mapped drive will update immediately.

The link
displayed in Excel may appear differently depending on how the workbook was
opened. The link may appear to match either the root UNC share or the root
drive letter that was used to open the file.

Scenarios that may cause links to not work as expected

There are several circumstances in which links between files can be inadvertently made to point to erroneous locations. The following are two of the most common scenarios.

Scenario 1:

You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.

You create links to a workbook that is stored at the mapped location after you open the file through that mapped drive.

You open the file by a UNC path.

As a consequence the link will be broken.

If you close the file without saving it, the links will not be changed. However, if you save the file before you close it, you will save the links with the current broken path. The folders between the root of the share and the mapped folder will be left out of the path. In the example above, the link would change to \\Server\Folder1. In other words, the Share name is eliminated from the path.

Scenario 2:

You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.

You open the file by a UNC path or a mapped drive mapped to a different folder on the share, such as \\Server\Share\Folder2.

As a consequence, the link will be broken.

If you close the file without saving it, the links will not be changed. However, if you save the file before you close it, you will save the links with the current broken path. The folders between the root of the share and the mapped folder will be left out of the path. In the example above, the link would change to \\Server\Folder1.