Formula Errors

How to Fix Excel INDIRECT #REF! Error with Specific Worksheet Names

Guest Writer Sep 28, 2026 875 views

Question details

The user is experiencing a #REF! error when using the INDIRECT function to reference a worksheet name that resembles a cell reference, such as 'C1_'.

Fix Excel INDIRECT #REF! Error with Worksheet Names Like C1_

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating dynamic formulas across different worksheets using the INDIRECT and ADDRESS functions.
Observed behavior: The formula successfully retrieves data for most worksheet names, but returns a #REF! error if the sheet name starts with certain letters and looks like a cell address (e.g., C1_).

Before you start

Verify that the worksheet you are trying to reference actually exists in your workbook and that the spelling in your formula exactly matches the sheet tab name.

Solution 1Recommended

Enclose the Worksheet Name in Single Quotation Marks

This solution prevents Excel from misinterpreting the worksheet name as a standard cell address or R1C1 reference.

When a worksheet name resembles a cell reference (like C1 or R1C1), Excel's INDIRECT function can get confused and fail to parse the reference correctly, resulting in a #REF! error. By wrapping the sheet name in single quotation marks, you force Excel to treat the string strictly as a worksheet name.

Select the formula cell

Click on the cell containing the INDIRECT formula that is returning the #REF! error.

Modify the formula string

Click into the formula bar and locate the section constructing the sheet name. Add a single quotation mark (') before and after the sheet name.

Apply the corrected syntax

For example, update your formula to look like this: =INDIRECT("'C1_'!"&ADDRESS(1,1)) or =INDIRECT(CONCATENATE("'C1_'!",ADDRESS(1,1))).

Press Enter

Hit Enter on your keyboard. The #REF! error should disappear and display the correct cell value.

Best Practice: It is highly recommended to always enclose worksheet names in single quotation marks within INDIRECT formulas. This not only prevents reference ambiguities but also handles sheet names that contain spaces or special characters.

Use WPS Spreadsheet to Manage Complex Formulas Smoothly

WPS Office provides a powerful, free Spreadsheet tool that fully supports advanced functions like INDIRECT, ADDRESS, and CONCATENATE. You can easily fix reference errors and manage cross-sheet data with standard Excel syntax.

1. Open your file in WPS Office: Launch WPS Spreadsheet and open the workbook containing your dynamic referencing formulas.
2. Select the error cell: Click on the cell displaying the #REF! error to highlight it.
3. Edit the formula: Navigate to the formula bar and insert single quotation marks around your sheet name string (e.g., "'SheetName'!A1").
4. Confirm changes: Press Enter to instantly calculate the correct value and resolve the reference error.

Full compatibility with Microsoft Excel formulas and .xlsx file formats.Reliable calculation engine for INDIRECT and dynamic cross-sheet referencing.Lightweight and fast, even when handling workbooks with many worksheets.Free to use with a familiar, tabbed user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does INDIRECT work for some sheet names and not others?

If a sheet name resembles a standard cell reference (like C1) or uses R1C1 formatting, the calculation engine gets confused between the sheet name and a cell address. Wrapping the sheet name in single quotation marks explicitly tells the software it is a sheet name.

Will adding single quotes break the formula if the sheet name doesn't resemble a cell reference?

No. Adding single quotes around the sheet name in an INDIRECT formula is perfectly safe for all sheet names and is highly recommended to prevent future errors if the sheet name is ever changed to include spaces.

Does the INDIRECT function work across closed workbooks?

No, the INDIRECT function only works when the referenced external workbook is currently open. If the external workbook is closed, the formula will return a #REF! error regardless of how perfectly the sheet name is formatted.

How do I dynamically reference a sheet name from another cell without getting a #REF! error?

You can concatenate the single quotes around the cell reference holding the sheet name. For example, if cell A1 contains the sheet name, use the formula =INDIRECT("'"&A1&"'!B1") to safely reference cell B1 on that sheet.

Excel & Data Problems