Formula Errors

How to Fix Excel INDEX Formula Returning #REF! When Source Workbook is Closed

Huma Ashraf Ch Sep 28, 2026 868 views

Question details

The user needs to prevent the INDEX formula from returning a #REF! error when the externally referenced workbook is closed.

Fix Excel INDEX Formula Returning #REF! When Source Workbook is Closed

Product: Microsoft Excel
Device & OS: not provided
Scenario: Pulling data from an external workbook using the INDEX function.
Observed behavior: The formula correctly retrieves data while the source workbook is open but throws a #REF! error when it is closed, particularly when INDIRECT or separate range boundaries are used.

Before you start

Ensure you have the exact file path of the source workbook and check if your formula relies on volatile functions like INDIRECT, which inherently require the external file to be open.

Solution 1Recommended

Use a Direct External Range Reference

Replace INDIRECT or split range boundaries with a single, direct, hard-coded external reference inside the INDEX function.

While functions like VLOOKUP natively support references to closed workbooks, combining INDEX with volatile functions (like INDIRECT) or using complex dynamic boundaries will break the connection when the source file is closed. Simplifying the formula to a direct static reference resolves this.

Select the error cell

Click on the cell containing the INDEX formula that is currently returning the #REF! error.

Remove INDIRECT functions

Highlight and delete any INDIRECT functions or complex dynamic boundaries used to build the file path in the formula bar.

Enter the direct file path

Type the exact, direct reference in the format: 'C:\Path\[WorkbookName.xlsx]SheetName'!$Range. Ensure the path is enclosed in single quotes if there are spaces.

Complete the formula

For example, update your formula to look like: =INDEX('C:\Data\[Test QA2.xlsx]Summary'!$A$5:$A$20,3). Press Enter to apply.

Why Direct References Work: Direct references allow the spreadsheet software to read the cached data of the closed workbook without needing to recalculate dynamic paths.

Efficient Spreadsheet Management

Fix External Link Formula Errors Easily with WPS Office

WPS Office offers robust formula support, including external data referencing with INDEX, MATCH, and VLOOKUP. It processes large datasets smoothly while maintaining full compatibility with Microsoft Excel formats, preventing common linking errors.

1. Open both workbooks: Launch WPS Spreadsheets and open both your main workbook and the external source workbook.
2. Start the INDEX function: In your main workbook, click the target cell and type =INDEX( to begin the formula.
3. Select the external range: Switch to the source workbook window and highlight the desired data range. WPS will automatically generate the correct, absolute external file path.
4. Finalize the formula: Complete your formula parameters (like the row or column number) and press Enter.
5. Close the source file: Save and close the source workbook. WPS Spreadsheets will retain the cached values without showing a #REF! error.

Seamless handling of complex formulas and external workbook links100% format compatibility with Microsoft Excel .xlsx and .xls filesFree, lightweight, and fast alternative for daily spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does VLOOKUP work with closed workbooks but my INDEX formula doesn't?

Both VLOOKUP and INDEX support external references to closed workbooks natively. However, if your INDEX formula is combined with functions like INDIRECT or uses dynamically constructed boundaries, Excel cannot resolve those dynamic paths when the source file is closed, resulting in a #REF! error.

Can I use the INDIRECT function with a closed workbook?

No. The INDIRECT function is a volatile function in Excel. It requires the referenced external workbook to remain open in the background to calculate the string reference. If the source file is closed, INDIRECT will always return a #REF! error.

How do I correctly format an external reference in Excel?

A standard external reference must include the full file path enclosed in single quotes, followed by the workbook name in square brackets, the sheet name, an exclamation mark, and the absolute cell range. Example: ='C:\Documents\[Data.xlsx]Sheet1'!$A$1:$B$10.

Excel & Data Problems