Function Problems

Quote Dynamic Excel Sheet Names in INDIRECT Formulas

Steven ParkerSteven Parker Nov 12, 2026 869 views

Question details

The user needs to reference worksheet names stored or generated in cells with an INDIRECT formula when names such as C1_2 or R1_2 are interpreted as references.

Quote Dynamic Excel Sheet Names in INDIRECT Formulas
Product
Microsoft Excel
Device & OS
not provided
Scenario
A formula builds a worksheet name dynamically and then uses INDIRECT to return the value in cell C24 on that worksheet.
Observed behavior
INDIRECT fails or manual references are not accepted when the sheet name resembles A1 or R1C1-style reference notation.
Before you start

Check that the generated worksheet name exactly matches an existing sheet tab, including underscores and spaces. In an INDIRECT text string, place single quotation marks around the sheet-name portion before adding the exclamation mark and cell address.

Solution 1Recommended

Wrap the dynamically generated sheet name in single quotes

Use this method when INDIRECT builds a reference to sheet names that can resemble cell addresses, such as C1_2 or R1_2. It also works safely with spaces and punctuation in sheet names.

Excel treats some unquoted sheet names as potential A1 or R1C1 references. Adding single quotation marks inside the text passed to INDIRECT forces Excel to read the value as a worksheet name.

1
Open the formula cell

Select the cell containing the failing formula, then click the Formula Bar or press F2 to edit it.

2
Add quoted sheet-name text

Place "'" before the expression that returns the sheet name and add "'!C24" after it. For a direct reference, use ='C1_2'!C24.

3
Use the corrected INDIRECT formula

Enter =INDIRECT("'"&IF(ISNUMBER(FIND(",",[@Panel])),CONCAT(LEFT([@Panel],FIND(",",[@Panel])-1),"_",RIGHT([@Panel],1)),[@Panel])&"'!C24") and press Enter.

4
Confirm the sheet and target cell

Verify that the sheet tab exists with the generated name and that cell C24 contains the value you expect to return.

Wrap the dynamically generated sheet name in single quotes
Quoted sheet names are more reliable: Using single quotes around every dynamically generated sheet name prevents errors with names containing spaces, punctuation, or text that resembles a cell reference.
WPS Spreadsheet formula help

Build Reliable Dynamic Sheet References in WPS Spreadsheet

WPS Spreadsheet supports INDIRECT formulas for dynamically constructed worksheet references. Add single quotation marks around the generated sheet name so names such as C1_2 are always treated as worksheet tabs rather than reference syntax.

  1. 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the INDIRECT formula.
  2. 2. Select the formula cell: Click the cell that should return the value from the dynamically named worksheet, then select the Formula Bar.
  3. 3. Insert single quotes around the sheet expression: Build the reference text as "'"&sheet_name&"'!C24" so the worksheet name is enclosed in single quotation marks.
  4. 4. Apply the completed formula: Enter =INDIRECT("'"&[@Panel]&"'!C24") for a simple panel-name field, or use your full sheet-name generation expression between the quote strings.
Use INDIRECT to retrieve values from dynamically named worksheets.Quote sheet names safely when they contain underscores, spaces, or reference-like text.Open and edit Microsoft Excel .xlsx and .xls workbooks with formula compatibility.Use a familiar spreadsheet interface for formulas, tables, and cell references.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the sheet name C1_2 need quotation marks in an Excel reference?

C1_2 can resemble reference syntax, especially under R1C1-style parsing rules. Use single quotes so Excel treats it as a worksheet name: ='C1_2'!C24.

Should I quote every sheet name used with INDIRECT?

Yes. Build INDIRECT references as "'"&sheet_name&"'!A1". The quotes are harmless for ordinary names and prevent issues with spaces, punctuation, and reference-like names.

How do I reference a sheet name stored in cell A1 with INDIRECT?

Use =INDIRECT("'"&A1&"'!C24"). If A1 contains C1_2, the resulting reference is 'C1_2'!C24.

Why does INDIRECT return #REF! after I add quotes?

Check that the text generated for the sheet name exactly matches a worksheet tab and that the target address is valid. INDIRECT returns #REF! if the sheet does not exist, the name contains an extra character, or the address is invalid.

Can INDIRECT reference a closed Excel workbook?

No. Standard INDIRECT does not evaluate references to a closed external workbook. Open the source workbook first, or use a different method such as Power Query for external data.