Forum Discussion
REF Broken Formula Link Issue
Hello!
I have created 6x files which contain formulas that link back to a specific cell from multiple external files all stored in the same drive. However, when opening each of these files, either half or all linking to external files break and become #REF erasing the formula that links to the external sheet.
Strangely, this doesn't happen consistently as sometimes only half of the links break across some or all files, sometimes all of them, and a mix of working and broken links in the same files. I'm not sure why this is happening and am finding it difficult to troubleshoot.
Has anyone experienced this before or know why this might be occurring? Any insight or help would be greatly appreciated!
Thank you!
2 Replies
This problem is more common in shared environments like network drives, OneDrive, or SharePoint, and it's often caused by Excel not being able to resolve the external file paths at the time it opens the workbook.
Here’s a breakdown how to prevent or troubleshoot it effectively:
Cause
Fix
Cloud syncing / delay
Open files after sync completes
Files renamed/moved
Update links manually or use relative paths
Structured references
Replace with static range references
Calculation before loading
Disable auto-updates or use Power Query
Inconsistent file order
Open source workbooks first
In the end it's pretty much the same as what the previous user "Kidd_Ip" suggested, just described a little differently 🙂.
If you can describe your storage setup (e.g. OneDrive, mapped network drive, local folder, operating system, etc.), you might be able to get a more detailed solution suggested in the forum.
My answers are voluntary and without guarantee!
Hope this will help you.
Please consider on below:
- File Path Changes – If the external files have been moved or renamed, Excel may struggle to locate them. Try updating the links manually via Data > Edit Links.
- Network or Drive Issues – If the files are stored on a shared drive or cloud service, intermittent connectivity issues could cause Excel to lose track of the links.
- Automatic Updates Disabled – Excel sometimes disables automatic updates for external links. Check Data > Edit Links and ensure the links are set to update.
- Corrupted Named Ranges – If named ranges are used in formulas, they may become corrupted. Try checking Formulas > Name Manager and removing any broken references.
- Protected Sheets or Workbooks – If the linked files are protected, Excel may not be able to access the formulas properly. Unprotect the sheets and test again.
- Excel Version Compatibility – If the files were created in an older version of Excel, compatibility issues might be causing the links to break. Try saving the files in a newer format.
