After opening my Microsoft Excel document why has Excel updated my sources?

My Excel sheet now shows #Ref what can I do now?

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 to help answer why has Excel updated my sources.

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:

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

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 \ServerFolder1. 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 \ServerShareFolder1.

You open the file by a UNC path or a mapped drive mapped to a different folder on the share, such as \ServerShareFolder2. 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 \ServerFolder1.

What Does This Mean and why has Excel updated my sources?

In a nutshell, this means if the user stores an Excel file with a mapped drive.
For example, F:financebudget.xlsx and associated the data based on this structure if a user then opens the file with the full-back such as \chtsi-fp01departmentfinancebudget.xlsx. Once the file is updated the links will change and unfortunately often breaks the Excel sheet.

Short-term Fix:

Microsoft TechNet Forums. – 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”.

IT Support in Harrogate

Carl Matthew Hamilton

Carl has the experience required to solve any IT queries & manage your IT infrastructure.

With over 20 years in the IT industry and a vast amount of experience and author of 300 posts and many more waiting to be published.

Subscribe To Receive The Latest News

Subscribe to find out first about new content.

See our Privacy Policy.

Need help or want to know more?

If you would like to learn more or are in need of support. Or would like to discuss working together, book an appointment or call 01423 423068

Interested and want to help smooth the process? Install our Onboarding toolYou can also try to reach us via our HubSpot Live Chat.

Schedule An Appointment

We would love to hear from you regarding any Outlook or Microsoft 365 Support, IT Support, Cyber Security, WordPress Website Design, or any Computer Support request you may have. Get the best out of Harrogate & Yorkshire IT Support.

Please note if you are a client and need to request IT Technical Support,  please use our Client Portal

Contact Us

Tel: 01423 423068

E-mail: hello@hamiltonsystems.co.uk

Online Support Portal: Client Portal

Need a quick chat or request? Try our HubSpot Chat from this page. Alternatively, use our WhatsApp Chat

Book with us

Loading...
Hamilton Systems

Looking For Better IT Support?

Unhappy with your IT Support or getting slow response times? Let us help provide a solution to suit you. Complete the form to arrange your callback & get your FREE IT audit.

Our email wasn’t performing very well so we asked Hamilton Systems to take a look & Carl proposed to migrate us to MS Exchange.

Carl was very helpful throughout & the migration went very well.

He was on hand to sort out any teething issues, taking calls early morning & into the night, to ensure we were up & running smoothly.

Would recommend for knowledge, attention to detail & great customer service.”

Ian Porter

Fotofabric Limited

Hamilton Systems

Book A 15 Minute Video Call

IT Support in Harrogate

Carl Hamilton

Managing Director
  • 22+ Years IT Experience

  • Business Phone Systems (VOIP)

  • Cyber Security Specialist

  • Experts in Outlook Support &  Migrations

  • IT Support in Harrogate

  • Outsourced IT

  • Social Media Management

  • Website Hosting & SEO Management

“Extremely helpful and proficient technical support. Provided one-off troubleshooting when I required support to recover Outlook. Carl was knowledgeable, polite, and patient in resolving my issue Highly recommended.” – Pras Legal Corp.

Book Your 15 Minute Video Discovery Call

Unhappy with your existing

IT Support?

Talk To Us

Call us 03330 509625

Published On: January 16th, 2021 / Categories: Microsoft 365, Office 365, Tutorials /