Formula Errors

Fix Excel INDIRECT #REF! Error for Specific Worksheet Names

Maira Mehtab Sep 22, 2026 870 views

Question details

The user is experiencing a #REF! error when attempting to use the INDIRECT and CONCATENATE functions to reference a specific worksheet named 'C1_'.

Product: Spreadsheet software
Device & OS: not provided
Scenario: Dynamically referencing another worksheet using the INDIRECT function combined with CONCATENATE and ADDRESS functions.
Observed behavior: The INDIRECT formula returns a #REF! error when the worksheet name starts with 'C' and resembles an R1C1 cell reference (e.g., 'C1_'), whereas replacing the 'C' with an 'X' yields correct results.

Before you start

Verify that the referenced worksheet actually exists in your current workbook and that there are no hidden spaces at the end of the sheet name.

Solution 1Recommended

Enclose the Worksheet Name in Single Quotation Marks

Wrap the dynamic worksheet name in single quotes to prevent the spreadsheet engine from misinterpreting the sheet name as an R1C1 or standard A1 cell reference.

When a worksheet name closely resembles an A1 or R1C1 cell reference (such as 'C1_'), Excel and other spreadsheet programs may get confused and try to evaluate the name as a cell address rather than a worksheet name. This ambiguity directly causes a #REF! error.

By explicitly enclosing the worksheet name in single quotation marks within your formula string, you force the program to recognize it strictly as a text string representing a sheet name, resolving the error instantly.

Locate the error-producing formula

Select the cell that currently displays the #REF! error to view its formula in the formula bar above the worksheet.

Add single quotes for standard INDIRECT references

If you are using a standard text string, add single quotes around the sheet name before the exclamation mark. For example, change =INDIRECT("C1_!A1") to =INDIRECT("'C1_'!A1").

Update CONCATENATE or dynamic structures

If you are building the reference dynamically using CONCATENATE, ensure the single quotes are included within the text strings. Change your formula to: =INDIRECT(CONCATENATE("'C1_'", "!", ADDRESS(1,1))).

Press Enter to recalculate

Hit Enter on your keyboard to apply the changes. The #REF! error should disappear and display the correct referenced value.

Best Practice: It is highly recommended to always enclose worksheet names in single quotes when using INDIRECT. This preemptively prevents errors if your sheet names contain spaces, punctuation marks, or happen to resemble cell references.

Master Complex Spreadsheets

Easily Handle Dynamic Formulas with WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and intelligent environment for managing complex dynamic formulas like INDIRECT, CONCATENATE, and ADDRESS without performance lags.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the file containing your problematic INDIRECT formulas.
2. Select the cell with the error: Click on the cell displaying the #REF! error to activate the formula bar.
3. Modify the formula syntax: Edit the formula to wrap your dynamic sheet name in single quotation marks, ensuring there is no ambiguity.
4. Apply and calculate: Press Enter to apply the change. WPS Spreadsheet will instantly parse the correct sheet name and display your data.

Fully compatible with standard Microsoft Excel formulas and functions.Intelligent formula error-checking helps identify syntax issues quickly.Lightweight architecture handles large, multi-sheet workbooks efficiently.Seamlessly supports both A1 and R1C1 reference styles.

microsoft office alternative - wps office

Frequently Asked Questions

Why does INDIRECT return #REF! only for specific sheet names?

If a worksheet name looks like an Excel column letter and row number (e.g., C1) or follows the R1C1 reference style, the spreadsheet's formula parser struggles to distinguish it from a cell address. Names starting with letters like 'X' might not trigger this if they don't form a recognizable cell reference pattern.

Do I always need single quotes around sheet names in INDIRECT?

While not strictly mandatory for single-word sheet names containing only letters, it is considered a universal best practice. Using single quotes ensures your formula won't suddenly break if someone renames the sheet to include spaces, numbers, or special characters later.

How does the ADDRESS function work in this scenario?

The ADDRESS function returns a cell reference as a text string (for example, row 1, column 1 returns '$A$1'). When combined with CONCATENATE and INDIRECT, it allows you to dynamically build a complete cell reference from separate text components, which INDIRECT then evaluates to fetch the actual cell data.

Excel & Data Problems