Tech
0

After Opening My Microsoft Excel Document Why Has Excel Updated My Sources? My Excel Sheet Now Shows #Ref

We assume by design as Microsoft was made aware of this behaviour back when Microsoft Office 2010 was popular. We think that Microsoft does not label this a bug as in a Microsoft KB article the behaviour is explained.

In this document Microsoft posts:

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:

  1. You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.
  2. You create links to a workbook that is stored at the mapped location after you open the file through that mapped drive.
  3. You open the file by a UNC path.
  4. 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:

  1. You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.
  2. 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.
  3. 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.

What Does This Mean?

In a nutshell this means if the user stores an Excel file with a mapped drive. For example F:\finance\budget.xlsx and associated the data based on this structure if a user then opens the file with the full back such as \\htsi-fp01\department\finance\budget.xlsx. Once the file is updated the links will change and unfortunately often breaks the Excel sheet.
Short-term Fix:

It can be resolved by turn off File “update Links on save”. File > Options > Advanced > Scroll down to “General”, then Click “Web Options” > Files, then uncheck “update Links on save”. Microsoft TechNet Forums

What's your reaction?
Love It
100%
Interesting
0%
Meh
0%
What?
0%
Hate It
0%
Sad
0%
About The Author
Carl Hamilton
Carl Hamilton
Entrepreneur, father, adventurer and looking to use education and technology to change the world.