Excel INDIRECT Returns #REF! When Source File Is Closed: Workarounds

Your Excel formulas using INDIRECT stop working and show a #REF! error. This happens after you close the workbook that contains the source data. The INDIRECT function cannot reference cells in closed external workbooks. This article explains why this error occurs and provides practical workarounds to maintain your dynamic references.

Why INDIRECT Fails with Closed Workbooks

The INDIRECT function builds a cell reference from a text string. It is designed to work with references inside the same open workbook. When you give INDIRECT a text string like “‘[SalesData.xlsx]Sheet1’!$A$1”, it tries to evaluate that reference in real time.

Excel’s calculation engine cannot pull live data from a workbook that is not open in memory. Therefore, INDIRECT returns a #REF! error because the target source is unavailable. This is a fundamental limitation of the function, not a bug. Functions like VLOOKUP or SUMIF can often reference closed workbooks, but INDIRECT cannot because it performs a secondary evaluation of the reference text.

Understanding Volatile and Non-Volatile Functions

INDIRECT is a volatile function. It recalculates every time Excel recalculates, even if its arguments haven’t changed. This design is part of why it cannot reach into closed files—it demands immediate access to the referenced cell’s current value. Non-volatile functions like INDEX can sometimes retrieve cached values from recently closed files, but INDIRECT does not have this capability.

Workarounds to Avoid the #REF! Error

You cannot make INDIRECT work directly on a closed file. Instead, use one of these methods to achieve a similar result without the error.

Method 1: Use CELL and a Defined Name for File Path

This method stores the closed workbook’s path in a named range, which INDIRECT can then use if the file is open. It helps manage the reference text but does not solve the closed-file issue on its own. It is most useful for building dynamic references when you plan to open the source file later.

1. Define a name for the file path
   Go to Formulas > Name Manager. Click New. In the Name field, type “SourceFilePath”. In the Refers to field, enter the full path in quotes, like ="C:\Reports\[SalesData.xlsx]". Click OK.
2. Build the reference text with the CELL function
   In a cell, use a formula to create the full reference. For example: =SourceFilePath & "'Sheet1'!A1". This will create a text string like “C:\Reports\[SalesData.xlsx]’Sheet1′!A1”.
3. Use INDIRECT only when the source is open
   Wrap the text from the previous step in INDIRECT: =INDIRECT(SourceFilePath & "'Sheet1'!A1"). This will work only when SalesData.xlsx is open. You will need a manual process or VBA to open the source file before calculation.

Method 2: Import Data with Power Query

Power Query imports and stores a snapshot of the external data in your workbook. This breaks the live link to the closed file, so INDIRECT is no longer needed. The data refreshes when you manually update the query.

1. Get data from your source file
   Go to Data > Get Data > From File > From Workbook. Browse and select your closed source workbook.
2. Load the data into your worksheet
   In the Power Query Editor, select the worksheet or table you need. Click Transform Data if you need to clean it. Click Close & Load to import the data as a table into a new sheet.
3. Reference the imported table locally
   Your data is now inside your current workbook. You can use standard formulas like VLOOKUP, INDEX, or even INDIRECT on this local table without any #REF! errors.

Method 3: Replace INDIRECT with INDEX and MATCH

For many lookup scenarios, a combination of INDEX and MATCH is a powerful and non-volatile alternative. This method can reference closed workbooks if the reference is written in a standard formula, not built via text.

1. Set up a standard external reference
   With the source workbook open, create a link to it. In a cell, type =, then switch to the source workbook and click on a cell. Press Enter. The formula will look like ='[SalesData.xlsx]Sheet1'!$A$1.
2. Build a dynamic lookup without INDIRECT
   To find a value based on a criteria, use MATCH to find the row and INDEX to return the value. For example: =INDEX('[SalesData.xlsx]Sheet1'!$B:$B, MATCH("Criteria", '[SalesData.xlsx]Sheet1'!$A:$A, 0)).
3. Close the source workbook and test
   Close the SalesData.xlsx file. The INDEX and MATCH formula should continue to display the last retrieved value without a #REF! error. It will update the next time you open both files.

If Your Workaround Does Not Solve the Problem

Excel Shows #REF! After Using Power Query

If you see #REF! in a Power Query table, the source file may have been moved or renamed. Open the Power Query Editor by clicking Data > Get Data > Launch Power Query Editor. Check the source step in the Applied Steps pane. Update the file path there, or use Data Source Settings to point to the new location.

INDEX and MATCH Returns #VALUE! on Closed Workbook

This can happen if you reference an entire column (like A:A) in a closed workbook for a function that performs array operations. Try limiting the reference to a specific range, like $A$1:$A$1000. Using full-column references with some functions requires the source workbook to be open.

You Need Truly Dynamic Sheet Names

If INDIRECT was used to switch between sheet names based on a cell value, you cannot replicate this with a closed source. The only reliable method is to use VBA to construct the formula string, open the source workbook in the background, perform the calculation, and then return the result.

Workaround Methods Comparison

Item	Power Query Import	INDEX and MATCH	CELL with Defined Name
Works with source closed	Yes	Yes, for cached values	No
Data is live-updating	No, requires manual refresh	Yes, when source is open	Only when source is open
Complexity to set up	Medium	Low	Low
Best for	Static data snapshots	Dynamic lookups	Managing file path strings

You can now choose a method to replace INDIRECT when working with closed files. For most data consolidation tasks, start with Power Query from the Data tab. If you need a simple cached lookup, the INDEX and MATCH combination is a strong alternative. Remember that using full-column references like A:A in external formulas can sometimes cause errors if the source workbook is closed.