The Situation
It's 2 AM, your spreadsheet dashboard is broken, and three people have already messaged asking why the summary tab shows nothing but #REF! errors. You trace it back to a batch of INDIRECT formulas that were working fine last week — until someone renamed a sheet from SalesData to Sales Data Q2.
The error you're seeing:
Error: Function INDIRECT parameter 1 value 'My Sheet!A1' is not a valid cell/range reference.
The formula looked like this:
=INDIRECT("My Sheet!A1")
Totally reasonable. Totally broken.
Why This Happens
Google Sheets requires single quotes around sheet names containing spaces, special characters (-, (, ), &), or names starting with a number. When you type a formula directly, Sheets adds those quotes automatically. The INDIRECT function gets no such help — it takes a plain text string and builds the reference itself, no auto-quoting.
Skip the quotes in your string and Sheets sees an invalid reference. That's where #REF! comes from.
Normal formula (Sheets handles quoting internally):
='My Sheet'!A1 ✓ Works
INDIRECT with raw string (no quoting):
=INDIRECT("My Sheet!A1") ✗ Breaks — #REF!
Debug Process
Step 1: Confirm the sheet name is the problem
Run a quick sanity check. In any blank cell, reference a sheet with a clean name — no spaces, no special characters:
=INDIRECT("Sheet1!A1")
That works? The issue is the sheet name format, not your formula logic or the target cell.
Step 2: Check what your INDIRECT string actually looks like
When the reference string is built dynamically from a cell value, isolate it first. Put this in a spare cell:
=A1&"!B2"
Check what it outputs. If you see My Sheet!B2, that's the broken string. You need 'My Sheet'!B2 — with single quotes around the sheet name.
Step 3: Identify sheet names that need quoting
These patterns all require single quotes inside your INDIRECT string:
- Contains a space:
Sales Data,Q1 Report - Contains special characters:
Revenue-2024,Data (Final),Team & Results - Starts with a number:
2024 Budget - Contains an apostrophe itself (the painful edge case — more on that below)
The Fix
Option 1: Hardcoded sheet name in INDIRECT
Wrap the sheet name in single quotes inside the string:
=INDIRECT("'My Sheet'!A1")
The outer double quotes are the string delimiters. The inner single quotes wrap the sheet name. Sheets parses 'My Sheet'!A1 correctly and returns the cell value you expected.
Option 2: Dynamic sheet name from a cell
Say cell A1 contains the text My Sheet. Concatenate single quotes around it:
=INDIRECT("'"&A1&"'!B2")
Breaking it down:
"'"— opening single quote (a one-character string)&A1&— the sheet name from cell A1"'!B2"— closing single quote plus the cell reference
This builds 'My Sheet'!B2, which INDIRECT parses without issue.
Option 3: Dynamic sheet name + dynamic cell reference
Both values come from cells:
=INDIRECT("'"&A1&"'!"&B1)
Here A1 holds the sheet name and B1 holds the cell address — something like C5 or D12.
Edge case: Sheet name contains an apostrophe
Someone named a tab Manager's Report. Now you have a quote-inside-a-quote problem. Inside the INDIRECT string, escape the apostrophe by doubling it:
=INDIRECT("'Manager''s Report'!A1")
Two consecutive single quotes ('') inside the wrapper signals a literal apostrophe — not the closing quote.
For a dynamic sheet name that might contain an apostrophe, use SUBSTITUTE to handle the escaping automatically:
=INDIRECT("'"&SUBSTITUTE(A1,"'","''")&"'!B1")
This gets messy fast. The cleaner long-term fix: rename the sheet and remove the apostrophe entirely.
Verification
After applying the fix:
- Visual check: The
#REF!error disappears and the cell shows the expected value from the target sheet. - Formula audit: Click the cell and check the formula bar — confirm the INDIRECT string has single quotes wrapping the sheet name.
- Test across rows: Dynamic formulas often span 20–50 rows. Spot-check a few where the sheet name differs to confirm the quoting logic holds in every case.
- Rename test: Temporarily rename the target sheet to a clean name (no spaces) — the formula should break again. Rename it back — it should work. This isolates the fix and rules out other variables.
Prevent It From Happening Again
This error hits hardest in dashboards that use INDIRECT for dynamic references. The formulas look fine during development — sheet names are clean, everything works. Then someone renames a tab to something "friendlier" and you're suddenly staring at 50 broken cells on a Monday morning.
A few habits that prevent this:
- Always quote sheet names in INDIRECT, even when the name looks safe.
=INDIRECT("'Sheet1'!A1")works identically to=INDIRECT("Sheet1!A1")— the quotes are harmless when unnecessary, but essential when the name changes. - Use a helper column to build the full reference string with quotes, then feed it into INDIRECT. Debugging one isolated cell beats auditing 50 nested concatenations.
- Set sheet naming rules in a comment on your control tab if other people edit the workbook. Even a single line — "No spaces in sheet names" — eliminates this entire class of bug.
- Consider named ranges instead of INDIRECT where possible. Named ranges follow their data when a sheet is renamed; INDIRECT formulas break silently.