Function Problems

Fix Excel INDIRECT Reference Error with Dynamically Defined Names

Natalie TaylorNatalie Taylor Oct 10, 2026 869 views

Question details

The user needs to fix a formula where passing a dynamically defined Excel name (which evaluates to an array) into an INDIRECT function results in a reference error.

How to Fix INDIRECT Reference Errors with Dynamically Defined Names in Excel
Product
Excel
Device & OS
not provided
Scenario
Attempting to use the INDIRECT function to reference a named range that is dynamically generated by another formula instead of a static cell reference.
Observed behavior
The formula works for static ranges but returns a #REF! error when an array is passed as the second reference, because INDIRECT cannot evaluate array data types.
Before you start

Open the Name Manager (Ctrl+F3) in your spreadsheet to check how your named range is defined, verifying whether it points to a static range or dynamically generates an array.

Solution 1Recommended

Use the OFFSET Function Instead of Nested INDIRECT

Replace the problematic INDIRECT function with OFFSET, which natively supports dynamic arrays and does not require text-based cell references.

The INDIRECT function is strictly designed to convert a text string into a cell reference. When a named range dynamically evaluates to an array rather than a direct cell reference, INDIRECT cannot process it and throws an error.

To bypass this limitation, you can use the OFFSET or INDEX functions. These lookup functions can handle dynamically sized ranges and arrays without requiring a strict text reference.

1
Identify the failing formula

Click on the cell containing your nested INDIRECT formula that is currently returning a #REF! error.

2
Modify the formula structure

Remove the outer INDIRECT function that is trying to evaluate the dynamic array name.

3
Apply the OFFSET function

Replace it with OFFSET. For example, instead of =INDIRECT(DynamicArrayName), structure your formula as =OFFSET(BaseCell, Rows, Cols, Height, Width) to dynamically point to the correct data.

4
Evaluate the result

Press Enter to execute the new formula. The array will now be processed correctly without triggering a reference error.

Use the OFFSET Function Instead of Nested INDIRECT
Formula Compatibility: Using OFFSET or INDEX instead of INDIRECT also makes your spreadsheet calculate faster, as INDIRECT is a volatile function that recalculates every time a change is made to the workbook.
Advanced Formula Calculation

Seamlessly Manage Dynamic Ranges and Formulas in WPS Spreadsheet

WPS Spreadsheet features a highly compatible calculation engine that effortlessly handles advanced lookup functions, volatile formulas, and dynamic arrays without slowing down your computer.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your dynamic arrays.
  2. 2. Access Name Manager: Go to the Formulas tab and click Name Manager to review how your dynamic names are configured.
  3. 3. Update your formulas: Type your new =OFFSET() or =INDEX() formulas directly into the formula bar. WPS Spreadsheet's smart autocomplete will guide you through the syntax.
Full compatibility with Microsoft Excel (.xlsx) formats and standard formulas.Intuitive Name Manager interface for easily tracking and modifying dynamic ranges.Lightweight architecture ensures fast recalculation of complex OFFSET and INDIRECT functions.Completely free to use with a familiar, easy-to-navigate user interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why does INDIRECT return a #REF! error with dynamic named ranges?

The INDIRECT function requires a valid text string that represents a direct cell reference. If your named range produces an array or is calculated dynamically rather than pointing to a direct reference, INDIRECT cannot process the array data type, resulting in a #REF! error.

Can I use INDIRECT to reference another workbook?

Yes, but with a major limitation: the external workbook you are referencing must be open. If you use INDIRECT to reference a closed workbook, the formula will immediately return a #REF! error.

What is the best alternative to INDIRECT for dynamic arrays?

OFFSET and INDEX are the best alternatives. Unlike INDIRECT, they do not require text strings to resolve cell references and can work directly with arrays and dynamically sized ranges, making them more stable for advanced spreadsheet modeling.