Formula Errors

How to Fix Excel INDIRECT Formula #REF! Errors with Criteria

Chanuka Geekiyanage Oct 9, 2026 869 views

Question details

The user encounters a #REF! error when attempting to use logical criteria inside an Excel INDIRECT formula.

How to Fix Excel INDIRECT Formula #REF! Errors with Criteria

Product: Excel
Device & OS: not provided
Scenario: Dynamically referencing a cell range using the INDIRECT function while also trying to evaluate a condition (such as checking for non-empty cells with <>"").
Observed behavior: Excel returns a #REF! error because the INDIRECT function attempts to process the embedded logical condition as part of the text reference string, which fails to evaluate into a valid cell range.

Before you start

Verify that your base cell references (like $I2 and $L3) are correctly formatted as text strings and point to existing sheets and ranges before modifying the formula.

Solution 1Recommended

Apply the Condition Outside the INDIRECT Function

To resolve the #REF! error, separate the logical condition from the range reference by placing it outside the INDIRECT function.

The INDIRECT function is designed strictly to convert text strings into valid cell references. It cannot evaluate logical operators or conditions like <>"" directly inside its arguments. Attempting to do so breaks the reference text, resulting in a #REF! error.

Locate the formula

Select the cell containing the INDIRECT formula that is displaying the #REF! error.

Remove embedded criteria

In the formula bar, delete the logical criteria (e.g., <>"") from inside the parenthesis of the INDIRECT function.

Restructure the formula

Apply the criteria outside the INDIRECT function. For example, instead of nesting it improperly, rewrite it to compare the returned array: =SUMPRODUCT(MAX((INDIRECT($I2&$L3)<>"")*ROW(INDIRECT($I2&$L3))))

Confirm the formula

Press Enter to evaluate the updated formula. The #REF! error should disappear and calculate the correct result.

Apply the Condition Outside the INDIRECT Function

Sheet Names with Spaces: If your dynamically referenced sheet names contain spaces or punctuation, you must wrap the sheet name portion in single quotation marks within the formula string (e.g., "'"&$I2&"'!").

Resolve Formula Errors Easily in WPS Spreadsheet

Use WPS Spreadsheet to Build and Troubleshoot Complex Formulas

WPS Spreadsheet offers deep compatibility with Microsoft Excel formulas, including INDIRECT, SUMPRODUCT, and dynamic array functions. You can easily troubleshoot and fix #REF! errors using its built-in formula evaluation tools.

1. Open your file: Launch WPS Spreadsheet and open the document containing the problematic INDIRECT formula.
2. Locate the error: Click on the cell showing the #REF! error to highlight it.
3. Evaluate the formula: Navigate to the 'Formulas' tab on the top ribbon and select 'Evaluate Formula' to see exactly where the reference breaks.
4. Update the criteria: Modify the formula in the formula bar to place the logical criteria outside the INDIRECT function, then press Enter.

Fully compatible with Microsoft Excel functions, including INDIRECT and SUMPRODUCT.Built-in Error Checking and Evaluate Formula tools to seamlessly trace #REF! errors.Free, lightweight, and fast spreadsheet processing for large datasets.Supports complex dynamic referencing across multiple sheets and workbooks.

microsoft office alternative - wps office

Frequently Asked Questions

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

The INDIRECT function requires the referenced external workbook to be open in the background. If the target workbook is closed, Excel cannot resolve the dynamic link, and INDIRECT will instantly return a #REF! error.

How do I handle spaces in sheet names using the INDIRECT function?

You must enclose the sheet name in single quotes within your text string for Excel to read it properly. For example, use =INDIRECT("'" & A1 & "'!B2") where cell A1 contains the name of the sheet with spaces.

Can I use wildcards inside an INDIRECT formula?

No, the INDIRECT function only converts text strings into exact cell or range references. It does not support wildcard characters (like * or ?) for conditional matching or partial sheet name lookups.

Excel & Data Problems