Forum Discussion
Excel formulas break (#REF error) when source workbooks aren't open
Hello Excel Experts,
I'm facing an issue with an Excel file containing multiple formulas that reference three other Excel workbooks. All these workbooks are stored in the same folder on my OneDrive cloud.
The problem is that when I open only the main workbook, the formulas referencing data from the other three files break and display a "#REF!" error, making the file unusable. However, if I also open those three source Excel workbooks simultaneously, the formulas automatically restore and work perfectly again.
Unfortunately, opening all workbooks each time isn't feasible, as I share this main workbook with colleagues who have access only to this specific file and not the other three source files. As a result, the formulas never work correctly for them.
How can I solve this issue so that formulas referencing external workbooks stored in OneDrive work properly without needing the referenced workbooks to be open simultaneously?
Thanks in advance for your help!
2 Replies
Using the extension markup could be a fix. Also, using DB containers to allow access may help. Local host requires a connector to different end users. Pending on the schematic of the workbooks, the basic connection may not always be useful and functional. Think of cleaning up the formulas and communication channels utilized.
The #REF! error in Excel occurs when formulas reference external workbooks that aren't open. This issue is common when using direct cell references (e.g., ='[Workbook.xlsx]Sheet1'!A1) because Excel cannot retrieve values from closed workbooks reliably, especially in OneDrive or SharePoint environments.
Convert External References to INDIRECT+TEXT
If you must use formulas, try INDIRECT, but this works only when all workbooks are stored locally (not on OneDrive).
Example:
Instead of:='[SourceFile.xlsx]Sheet1'!A1
Use:
=INDIRECT("'[SourceFile.xlsx]Sheet1'!A1")
Or…Use Power Query
Power Query can import data from closed Excel workbooks, avoiding #REF! errors.
Steps to Use Power Query:
Open your main workbook.
Go to Data > Get Data > From File > From Workbook.
Select the external workbook you need data from.
Choose the required sheet/table and Load it into your main workbook.
Use Excel formulas on the imported data (instead of direct references).
My answers are voluntary and without guarantee!
Hope this will help you.
