How to Fix #REF! Errors From Closed External Excel Workbooks
Question details
The user encounters #REF! errors in a main workbook when formulas referencing external OneDrive source workbooks fail to evaluate while the source files are closed.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Sharing a main workbook where formulas rely on external source workbooks stored in OneDrive, preventing colleagues from viewing the data unless all files are opened.
- Observed behavior
- Formulas return #REF! errors when the external source workbooks are closed, but function normally when all associated source files are open.
Verify that you have stable access to the OneDrive folders where the source workbooks are stored, and that the file paths have not recently changed.
Use Power Query to Import External Data
Replacing direct external formulas with Power Query imports creates a stable connection, preventing #REF! errors without requiring the source files to be open.
Direct external references to files in OneDrive or SharePoint frequently fail when the source workbook is closed. Using Power Query fetches the data into your main workbook securely as a local table.
Note that functions like INDIRECT are not dependable for this scenario, as they inherently require the referenced external workbook to be open locally.
In your main Excel workbook, navigate to the 'Data' tab on the ribbon, click on 'Get Data', select 'From File', and then choose 'From Workbook'.
Locate and select your external source workbook stored in the synced OneDrive directory, then click 'Import'.
In the Navigator window, choose the specific sheets or tables you need to reference from the source file, then click 'Load' to import the data into your main workbook.
Update the #REF! formulas in your main workbook to reference the newly imported local tables instead of the direct external file paths.
Try WPS Office for Seamless Spreadsheet Management
If you are frustrated by complex reference errors or syncing issues in Microsoft Excel, consider switching to WPS Office. It provides a lightweight, highly compatible, and user-friendly spreadsheet environment without the heavy subscription costs.
Frequently Asked Questions
Why does the INDIRECT function return a #REF! error with closed workbooks?
The INDIRECT function is volatile and requires the referenced external workbook to be open in the background to evaluate the file path. If the source file is closed, the application cannot resolve the reference, resulting in a #REF! error.
How can I update my Power Query data if the source file changes?
You can refresh the imported data by going to the 'Data' tab and clicking 'Refresh All'. This will pull the latest information from the connected external workbooks without needing to physically open them.
Can colleagues view the data if they don't have access to the source file?
Yes. When you use Power Query to load the data into the main workbook, the imported values are saved locally within the file. Colleagues can view the current cached data, though they will not be able to refresh it without permissions to the source file.
