Function Problems

How to Use INDIRECT with a Named Range in Another Excel Workbook

Bushra Parveen Oct 10, 2026 869 views

Question details

The user needs to determine whether a specific record exists in a large external workbook by using the INDIRECT function combined with a named range.

Product: Excel
Device & OS: not provided
Scenario: Attempting to build a dynamic cross-workbook reference using INDIRECT and a named range to check if certain items exist in a larger master spreadsheet.
Observed behavior: The formula returns a #SPILL! error instead of the expected result, likely because the named range refers to a dynamic array or the text reference is improperly constructed.

Before you start

Ensure the external source workbook is actively open in your spreadsheet software; the INDIRECT function cannot resolve references to closed workbooks and will result in a #REF! error.

Solution 1Recommended

Correct the INDIRECT Text Syntax to Target a Specific Cell

Fix the #SPILL! error by ensuring your INDIRECT function resolves to a valid single cell or exact range rather than an open-ended dynamic array.

The INDIRECT function works by evaluating a text string and converting it into a valid cell reference. If the named range 'Sheets' evaluates to an array and the formula doesn't have room to output multiple values, a #SPILL! error occurs.

Verify your Named Range

Go to the Formulas tab, open the Name Manager, and ensure the named range 'Sheets' refers to a single cell or a specific static range, not a dynamic spill array.

Build the text reference carefully

Construct the external reference string properly by including the workbook name in brackets, followed by the sheet name and cell. Example: =INDIRECT("'["&A2&"]"&Sheet3!$C$2&"'!"&Sheet3!$B$2).

Apply the formula

Press Enter to evaluate the formula. If structured correctly and the source workbook is open, the spill error will resolve.

Correct the INDIRECT Text Syntax to Target a Specific Cell

Syntax Requirements: When referencing external workbooks, the workbook name must be enclosed in square brackets [ ], and the entire workbook and sheet path must be enclosed in single quotes ' ' if it contains spaces.

Solution 2

Use XMATCH or XLOOKUP for Record Existence Checks

Since the ultimate goal is just to check if a record exists in another workbook, using XMATCH or XLOOKUP is far more robust and efficient than INDIRECT.

Advanced Spreadsheet Functions

Manage Cross-Workbook Formulas Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array formulas and functions like INDIRECT, XLOOKUP, and XMATCH. It offers seamless cross-workbook data linking without the typical slowdowns experienced with volatile functions in large datasets.

1. Open your workbooks in WPS Spreadsheet: Launch WPS Office and open both your working file and the external master file in the same window.
2. Input the existence check formula: Use the highly compatible XMATCH or INDIRECT formulas directly in your cell to reference the external workbook.
3. Process data seamlessly: Press Enter to instantly execute the query, filtering out records that do not exist across your connected sheets.

100% compatible with Microsoft Excel formulas (.xlsx format).Efficient calculation engine for large workbooks and arrays.Native support for XLOOKUP, XMATCH, and dynamic arrays without unexpected spill errors.Free and lightweight alternative to heavy spreadsheet applications.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDIRECT formula return a #REF! error when referencing another workbook?

The INDIRECT function cannot evaluate references to external workbooks that are currently closed. To fix the #REF! error, ensure the source workbook is open in the same application instance.

What causes a #SPILL! error when using named ranges?

A #SPILL! error occurs when a formula returns an array of multiple values, but there isn't enough empty space in the adjacent cells to display them. With INDIRECT, it means your named range resolves to multiple cells instead of a single value.

Can I reference a closed workbook dynamically without using INDIRECT?

Yes. While INDIRECT does not work with closed workbooks, you can use Power Query to pull dynamic data, or use VBA macros to update standard external links. Alternatively, standard formulas like INDEX or XLOOKUP work with closed workbooks as long as the path remains static.

Excel & Data Problems