Fix Excel INDIRECT Reference Error with Dynamically Defined Names
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.

- 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.
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.
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.
Click on the cell containing your nested INDIRECT formula that is currently returning a #REF! error.
Remove the outer INDIRECT function that is trying to evaluate the dynamic array name.
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.
Press Enter to execute the new formula. The array will now be processed correctly without triggering a reference error.

Convert Dynamic Arrays to Static Named Ranges
If you must use INDIRECT, redefine your named range to refer directly to static cells rather than evaluating as an array.
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. Open your workbook: Launch WPS Spreadsheet and open the file containing your dynamic arrays.
- 2. Access Name Manager: Go to the Formulas tab and click Name Manager to review how your dynamic names are configured.
- 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.

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.




