Formula Errors

Fix Excel INDEX Returns #REF Error When Referencing a Closed Workbook

Maira Mehtab Sep 22, 2026 868 views

Question details

The user experiences a #REF! error when an INDEX formula references data in a closed external workbook, whereas VLOOKUP references continue to work properly.

Product: Excel
Device & OS: not provided
Scenario: Pulling specific data arrays from an external workbook using the INDEX function.
Observed behavior: The INDEX formula calculates correctly while the source workbook is open, but immediately returns a #REF! error as soon as the source workbook is closed.

Before you start

Verify the exact file path of your external source workbook and check your current formula to see if the INDIRECT function is being used to dynamically construct the reference string.

Solution 1Recommended

Replace INDIRECT with a Direct External Reference

The INDIRECT function does not support referencing closed external workbooks. Replacing it with a direct, hardcoded file path reference will resolve the #REF! error.

When using the INDIRECT function to dynamically construct an external range string, Excel requires the source workbook to be actively open in the background to evaluate the text into a real reference. If the file is closed, Excel cannot resolve the dynamic string, resulting in a #REF! error. Standard array references within INDEX do not have this limitation as long as they are hardcoded.

Inspect the error cell

Select the cell containing the #REF! error and look at the formula bar to review the current formula.

Identify the INDIRECT function

Check if your external file path or range is wrapped inside an INDIRECT() function. This is the root cause of the broken link when the file is closed.

Remove INDIRECT and construct the direct path

Delete the INDIRECT wrapper and manually type the direct path to the closed workbook. The format must enclose the path and filename in single quotes, with the filename in brackets, like so: 'C:\Data\[Test QA2.xlsx]Summary'!$A$5:$A$20

Update the INDEX formula

Combine the direct external reference with your INDEX function. Your final formula should look similar to: =INDEX('C:\Data\[Test QA2.xlsx]Summary'!$A$5:$A$20, 3)

Apply and test

Press Enter to apply the updated formula. Ensure the source workbook is closed to verify that the value remains visible without generating a #REF! error.

Simplifying Array References: By simplifying the array reference and strictly using direct paths, your external links will safely maintain their cached values even after source workbooks are closed.

Efficient Spreadsheet Management

Handle External References Seamlessly in WPS Spreadsheet

WPS Office fully supports advanced formulas, external data linking, and cross-workbook references. You can easily generate, manage, and update links to closed files without dealing with complex formula workarounds.

1. Open workbooks: Open both your main destination workbook and the source workbook simultaneously in WPS Spreadsheet.
2. Generate reference automatically: Type '=INDEX(' in your target cell, then switch to the source workbook window and highlight your desired data range. WPS will automatically insert the correct external direct reference syntax.
3. Finalize and close: Finish the formula with the necessary row or column indexes and press Enter. You can now close the source workbook, and WPS Spreadsheet will keep the linked data intact without returning an error.

High compatibility with Microsoft Excel (.xlsx, .xls) file formats.Flawless execution of advanced data lookups like INDEX, MATCH, and VLOOKUP.Built-in 'Edit Links' tool to safely manage and refresh external workbook connections.Lightweight software that processes heavy data links and arrays faster.

Frequently Asked Questions

Why does VLOOKUP work with a closed workbook but INDIRECT does not?

Standard lookup functions like VLOOKUP, INDEX, and MATCH can read cached data structures directly from a closed workbook using hardcoded external references. However, INDIRECT is a volatile function that evaluates text strings dynamically. This evaluation process requires active memory allocation of the target file, which is only possible when the workbook is open.

How do I update the values if the closed external workbook's data changes?

When you open the destination workbook, the software will usually prompt you to 'Update Links'. Clicking 'Update' fetches the latest data from the closed file. Alternatively, you can navigate to the Data tab, select 'Edit Links', and click 'Update Values' to manually refresh the connection.

Are there any alternatives to INDIRECT for creating dynamic external references to closed files?

If you need dynamic links without keeping workbooks open, consider using Power Query. You can use Power Query to import and refresh the external data into a hidden local worksheet within your current workbook. Your INDEX formulas can then point to that local hidden sheet dynamically.

Excel & Data Problems