#REF! in INDIRECT: Sheet Name Typos and Apostrophe requirements

An #REF! error in an INDIRECT formula usually means the text string supplied to the function fails to assemble a valid cell or range address. When referencing cells on another worksheet, Excel requires specific punctuation depending on the sheet name. If your target sheet name contains spaces, numbers, or symbols, omitting enclosing single apostrophes (') breaks the address syntax. When Excel evaluates the invalid string, it cannot find the target coordinates and returns #REF!.

Quick Answer

Wrap sheet names in single apostrophes inside the INDIRECT string: =INDIRECT("'" & A2 & "'!B5"). Sheet names containing spaces, hyphens, or numbers starting the name require single quotes to form a valid reference. If the error persists, check the worksheet tab for hidden trailing spaces using =TRIM(A2).

---

The Single-Quote Rule in Excel Syntax

When you write a normal cross-sheet reference, Excel manages the syntax automatically:

• Sheet without spaces: =Sheet1!B5
• Sheet with spaces: ='North America'!B5
• Sheet with hyphens: ='Q1-Results'!B5

Because INDIRECT evaluates a text string that you construct, Excel will not add those required apostrophes for you.

If cell A2 contains the text North America, consider this formula:

=INDIRECT(A2 & "!B5")

Excel evaluates the concatenated string as:
INDIRECT("North America!B5")

Because the space separates “North” and “America”, Excel’s formula engine misinterprets the text as an invalid named range or malformed address, immediately throwing #REF!.

---

The Universal Fix: Add Single Quotes to All References

To prevent syntax errors regardless of what the sheet is named, always include single apostrophes around the sheet name variable:

=INDIRECT("'" & A2 & "'!B5")

Excel evaluates this as:
INDIRECT("'North America'!B5")

Formula Breakdown:
  "'"   --> Opening single quote enclosed in double quotes
& A2    --> The cell containing the sheet name
& "'!"  --> Closing single quote and exclamation point enclosed in double quotes
& "B5"  --> The target cell coordinate

Even if the sheet name in A2 is a single word like Sales (which does not strictly require quotes), adding single apostrophes is valid syntax and will not cause an error ('Sales'!B5 resolves identically to Sales!B5).

---

Diagnostic Step: Check for Tab Name Typos and Trailing Spaces

If your formula includes single apostrophes and still returns #REF!, the sheet name string in your cell does not match the actual sheet tab name.

1. Trailing Spaces on Worksheet Tabs

The most common hidden issue occurs when a user renames a sheet tab and accidentally presses the Spacebar:

• Cell A2 displays: "Data" (length = 4)
• The actual tab is named: "Data " (length = 5)

Because the strings do not match, INDIRECT looks for a non-existent sheet and returns #REF!.

• How to test:
  Double-click the worksheet tab at the bottom of Excel. Press the Right Arrow key. If the cursor moves past the last visible letter, a space is present. Delete it.

2. Verify with a Direct Formula

To confirm whether the name in cell A2 matches the tab:

1. Click an empty cell on your working sheet.
2. Type = and navigate to the target sheet.
3. Click cell B5 and press Enter.
4. Examine the formula bar:
   ='Marketing Dept'!B5
5. Compare the text between the single quotes in that formula directly against the text typed in your INDIRECT cell.

---

Special Characters That Cause #REF!

Certain characters in sheet names create syntax conflicts inside INDIRECT:

Character Type	Example Sheet Name	Why It Causes #REF!	Correct INDIRECT Construction
Spaces	West Region	Space breaks argument boundaries	=INDIRECT("'" & A2 & "'!A1")
Punctuation / Hyphens	2026-Budgets	Minus sign is read as an operator	=INDIRECT("'" & A2 & "'!A1")
Numbers as Sheet Names	2026	Read as a numeric constant	=INDIRECT("'" & A2 & "'!A1")
Apostrophe in Name	Client's Data	Extra apostrophe terminates string early	Avoid apostrophes in sheet tabs entirely

Warning: If a worksheet tab contains a literal apostrophe (e.g., Dave's Data), standard single-quoting will break because Excel reads the sheet’s apostrophe as the end of the name. Avoid apostrophes in worksheet tab names when using INDIRECT.

---

The Silent Vulnerability of INDIRECT

Standard formulas update automatically when a worksheet tab is renamed. If you rename Sheet1 to Summary, a formula like =Sheet1!A1 updates to =Summary!A1 instantly.

INDIRECT does not update because its argument is a static text string. If someone renames a sheet, every INDIRECT formula referencing the old name will break with #REF!.

For strategies to build robust dynamic summaries that do not break when sheets change, read #REF! in Summary Sheets: Dynamic sheet referencing failures. If your INDIRECT formula is trying to access a sheet in another workbook, see #REF! in INDIRECT: Referring to a Closed Workbook. To trace all reference breaks across a workbook, see How to Fix #REF! Errors in Excel: A Comprehensive Structural Repair Guide.