Excel formulas break (#REF error) when source workbooks aren't open

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.