Fix Excel #REF! Errors in Linked OneDrive Workbooks
Question details
Users experience #REF! errors in Excel functions when referencing external workbooks hosted on OneDrive or SharePoint.
- Product
- Microsoft Excel, OneDrive, SharePoint
- Device & OS
- not provided
- Scenario
- Opening a linked destination workbook stored in OneDrive or SharePoint independently while the source workbook is closed or accessed separately.
- Observed behavior
- Functions such as SUMIF, INDEX, and MATCH return #REF! errors, despite the formulas working properly when both files are closed or stored locally.
Ensure that both the source and destination workbooks are fully synchronized in OneDrive or SharePoint, and confirm that no recent changes have been made to the file names or folder paths.
Isolate Cloud Issues by Testing Files Locally
Determine if the #REF! error is caused by a OneDrive or SharePoint sync limitation by testing the workbooks on your local hard drive.
Cloud synchronization delays or path formatting changes in OneDrive and SharePoint can sometimes sever the links required by certain Excel formulas, leading to unexpected #REF! errors.
Download both the source and destination workbooks from your OneDrive or SharePoint directory to a local folder on your computer's hard drive.
Launch Excel and open the local destination workbook first, followed immediately by opening the local source workbook within the same Excel instance.
Check the cells containing the SUMIF, INDEX, or MATCH functions to see if the #REF! errors have resolved and the data displays correctly.
If the local files work properly, the issue lies with cloud synchronization. Update your OneDrive sync client and Microsoft Excel application to their latest versions.
Submit a Microsoft 365 Support Request
If local testing resolves the issue but the error persists strictly in SharePoint or OneDrive, escalate the issue as a potential platform defect.
Switch to WPS Office for Reliable Spreadsheet Management
Dealing with complex sync issues and #REF! errors in Microsoft Office can disrupt your workflow. WPS Office provides a lightweight, highly compatible alternative that processes complex formulas and linked workbooks reliably, ensuring your data remains accurate without sync-induced errors.
- 1. Download and Install WPS Office: Visit the official WPS website, download the free suite, and complete the installation on your device.
- 2. Open WPS Spreadsheet: Launch the WPS Spreadsheet application to begin managing your data and linked files.
- 3. Load Your Excel Workbooks: Open your existing .xlsx workbooks; WPS Spreadsheet will seamlessly read your formulas and maintain your external data links.
Frequently Asked Questions
Why do SUMIF and INDEX/MATCH formulas return #REF! when linking closed workbooks?
Certain Excel functions, including SUMIF, SUMIFS, COUNTIF, and INDIRECT, cannot evaluate external references to closed workbooks. If the source workbook is closed or its path is interrupted by cloud sync mechanics, Excel cannot retrieve the array data, resulting in a #REF! or #VALUE! error.
Does moving a file in OneDrive break Excel formula links?
Yes. If you move or rename a source workbook within OneDrive or SharePoint while the destination workbook is closed, the absolute path used in the formula breaks. This leads to #REF! errors the next time you open the destination file.
How do I update broken links in Excel?
Navigate to the 'Data' tab on the Excel ribbon and click 'Edit Links' in the Queries & Connections group. Select the broken reference from the list, click 'Change Source', and browse to the new file location to restore the connection.
How can I prevent #REF! errors when using cloud storage?
To minimize link-breaking errors, ensure your OneDrive sync client is always up to date. Keep linked files in the same directory when possible, and always open both the source and destination workbooks simultaneously before renaming files or making structural changes.
