#REF! in INDIRECT: Referring to a Closed Workbook

An #REF! error produced by the INDIRECT function when pointing to another file means the target workbook is closed. While standard Excel formulas can read and display cached values from closed spreadsheets, INDIRECT is architected differently. It evaluates text strings into active cell pointers in memory. If the referenced file is not open in the same Excel session, Excel cannot resolve the dynamic reference address and immediately returns #REF!.

Quick Answer

INDIRECT cannot read closed workbooks. To clear the #REF! error immediately, open the referenced source workbook on your desktop. If the workflow requires querying files that remain closed, replace INDIRECT with standard direct links (='[Book.xlsx]Sheet1'!A1), Power Query, or an external data connection.

---

Why INDIRECT Fails on Closed Workbooks

To understand why this error occurs, compare how Excel handles standard external references versus dynamic references:

• Standard External Link: ='C:\Data\[Sales_2026.xlsx]Summary'!$B$2 When you save a workbook containing this formula, Excel stores the last known value in a local file cache. When you reopen the workbook, Excel displays that cached number even if Sales_2026.xlsx is closed on your hard drive or network share.
• Dynamic INDIRECT Link: =INDIRECT("'[Sales_2026.xlsx]Summary'!B2") INDIRECT takes a raw text string and evaluates it into a live cell coordinate. It does not use Excel’s external link cache. Because it must query the open memory space of Excel to locate the target sheet and cell, closing Sales_2026.xlsx instantly cuts the memory connection. Excel can no longer verify that the address exists, so it halts and returns #REF!.

For a broader look at external workbook link behavior, see #N/A across External Workbook Links.

---

Step-by-Step Diagnostic Check

Step 1: Diagnose the Error Condition

Before restructuring your spreadsheet, confirm that the closed file is the sole reason for the #REF! error.

1. Note the exact name of the file referenced inside your INDIRECT formula.
2. Open that file in Excel.
3. Switch back to your working sheet and press F9 (or Ctrl + Alt + F9) to force recalculation.
4. Result:
   • If the #REF! error immediately transforms into the expected value, the formula syntax is correct, and the failure is entirely caused by the closed-workbook limitation of INDIRECT.
   • If the #REF! error remains while the file is open, you have a syntax error, such as missing single quotes around sheet names with spaces or a typo in the sheet tab name. To fix syntax issues, consult #REF! in INDIRECT: Sheet Name Typos and Apostrophe requirements.

---

Step 2: Choose a Long-Term Workaround

Because INDIRECT cannot bypass this architectural limitation natively, you must change how your workbook retrieves data from closed sources.

Method 1: Convert to Direct External References

If you do not strictly require runtime text assembly, use direct external formulas.

Instead of:
=INDIRECT("[" & A1 & ".xlsx]Sheet1!B5")

Write the formula directly:
='[Regional_East.xlsx]Sheet1'!$B$5

If you have dozens of formulas to update based on a cell value, use Excel’s Find and Replace (Ctrl + H) to update file paths in bulk across standard links. Standard links update without throwing #REF! when source files are closed.

Method 2: Import Data Using Power Query (Recommended)

If your model dynamically pulls data from multiple external workbooks across a shared drive or SharePoint folder, Power Query is the modern alternative to INDIRECT.

1. Go to the Data tab.
2. Select Get Data > From File > From Excel Workbook (or From Folder).
3. Connect to your external source file.
4. Transform and load the table directly into your workbook.

Power Query reads closed files from local disks, OneDrive, SharePoint, and network drives without requiring the files to be open. It refreshes on command or on file open, entirely eliminating the risk of #REF! breaks.

If external file paths break after files are moved, follow the repair procedures in #REF! in Linked Workbooks: Broken File Paths/Shared Drive moves.

Method 3: VBA Custom Functions (ExecuteExcel4Macro)

If you must retrieve values dynamically from closed workbooks using cell-driven strings without Power Query, native VBA can execute an older Excel 4 macro function that reads closed file headers:

Function GetClosedValue(path As String, file As String, sheet As String, ref As String)
    Dim arg As String
    If Right(path, 1) <> "\" Then path = path & "\"
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Address(True, True, xlR1C1)
    GetClosedValue = ExecuteExcel4Macro(arg)
End Function

This user-defined function retrieves the data from closed disk files where =INDIRECT() fails.

---

Additional Risk: Volatility and Performance Lag

Even when the target workbook remains open, INDIRECT is a volatile function. Every time any cell in the workbook changes, Excel recalculates every INDIRECT formula, even if the referenced data did not change.

Using hundreds of INDIRECT formulas across external files can cause severe calculation delays and workbook freezing. To understand how volatile functions impact workbook stability, see The Volatile Function Bloat: How INDIRECT and OFFSET kill system performance. For a full structural review of reference errors, consult How to Fix #REF! Errors in Excel: A Comprehensive Structural Repair Guide.