Formula Errors

Fix Excel INDIRECT Returning #REF! When Opening External Workbook via Hyperlink

Camila Milosovich Oct 12, 2026 870 views

Question details

The user needs to resolve a #REF! error returned by an INDIRECT formula when referencing an external workbook that was opened using a HYPERLINK formula.

How to Fix INDIRECT Returning #REF! When Opening External Workbooks via Hyperlink

Product: Microsoft Excel
Device & OS: not provided
Scenario: Referencing data dynamically in a separate, external Excel workbook using the INDIRECT function.
Observed behavior: The INDIRECT formula works perfectly when the source file is opened directly, but returns a #REF! error when the file is opened via a HYPERLINK formula or if the external file is password-protected.

Before you start

Ensure that the external workbook is fully loaded in the same Excel instance and that you have typed the exact file path, workbook name, and sheet name without any spelling mistakes.

Solution 1Recommended

Isolate the Issue with a Hard-Coded Reference

Test the formula by replacing dynamic references with a static text string to verify if the file path and workbook identity are correct.

External references can behave inconsistently depending on how the workbook is opened. Isolating the reference string helps confirm whether the issue is with the INDIRECT syntax itself or the HYPERLINK opening method.

Open Both Workbooks

Open both your destination workbook (where the formula is) and the external source workbook directly using File > Open.

Create a Static Reference

Select a blank cell and type a standard, direct reference to the external workbook, such as ='[SourceBook.xlsx]Sheet1'!A1, and press Enter.

Test with Hard-Coded INDIRECT

In another cell, type a simple INDIRECT formula using the exact text string of that reference: =INDIRECT("'[SourceBook.xlsx]Sheet1'!A1").

Compare the Results

If the hard-coded INDIRECT returns the correct value, the #REF! error is specifically triggered by how the dynamic text is constructed or how the HYPERLINK formula interacts with Excel's calculation engine.

Isolate the Issue with a Hard-Coded Reference

INDIRECT Limitation: The INDIRECT function strictly requires the referenced external workbook to be open. If it is closed, the formula will always return a #REF! error.

Solution 2

Address Password Protection Limitations

Ensure password-protected workbooks are unlocked and fully loaded before calculating the INDIRECT formulas.

Solution 3

Submit Feedback via the Feedback Hub

If the #REF! error persists despite the file being open and unprotected, report the hyperlink-specific behavior as a bug to Microsoft.

Free Microsoft Office alternative

Try WPS Office for Reliable Spreadsheet Calculations

If you are experiencing persistent bugs with formulas like INDIRECT and HYPERLINK in Microsoft Excel, consider switching to WPS Office. It provides a highly compatible, stable environment for handling complex data references across multiple workbooks.

1. Download and Install: Visit the official WPS website, download WPS Office for free, and run the quick installer.
2. Open Excel Files: Launch WPS Spreadsheet and go to Menu > Open to load your existing .xlsx workbooks.
3. Manage Formulas with Ease: Continue using your INDIRECT and HYPERLINK formulas with a reliable and fast calculation engine.

Completely free to use with a lightweight installationHigh format compatibility with Microsoft Excel (.xlsx, .xls) filesRobust formula engine that consistently handles external data referencesFamiliar tabbed interface allowing you to open multiple workbooks in one window seamlessly

microsoft office alternative - wps office

Frequently Asked Questions

Why does the INDIRECT function return #REF! with external workbooks?

The INDIRECT function can only read data from an external workbook if that workbook is currently open in the same application instance. If the external file is closed, INDIRECT cannot resolve the address and will instantly return a #REF! error.

Can I use INDIRECT with a password-protected workbook?

Using INDIRECT to reference a password-protected workbook can be problematic. If the workbook is not fully opened and unlocked before the calculation runs, Excel cannot fetch the data, resulting in a #REF! error. Always open and unlock the protected file manually first.

Is there an alternative to INDIRECT for referencing closed workbooks?

Yes. If you need to pull data from closed workbooks, avoid INDIRECT. Instead, use the INDEX and MATCH functions with direct external links, or use Power Query (Data > Get Data > From File > From Workbook) to import the external data reliably.

Does opening a file via HYPERLINK change how Excel sees the file?

Sometimes. Opening a file via a HYPERLINK formula can cause the file to open in a protected view or a different security context, which may prevent background calculation engines (like the one used by INDIRECT) from recognizing that the file is fully open.

Excel & Data Problems