Excel INDIRECT Function: Dynamic Cell References
INDIRECT converts text strings into cell references. Use it to dynamically reference sheets, create flexible formulas, and build cascading dropdowns.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Sum values from a sheet whose name is in cell A1"
=SUM(INDIRECT("'"&A1&"'!B2:B100"))INDIRECT builds a sheet reference from the text in A1. If A1 contains "January", this becomes =SUM('January'!B2:B100). Change A1 to "February" and it automatically references the February sheet.
Step-by-step breakdown
- A1 contains the sheet name as text (e.g., "January")
- "'"&A1&"'!B2:B100" builds the string: 'January'!B2:B100
- INDIRECT converts this text string into a real cell reference
- SUM then totals the referenced range
- Single quotes around the sheet name handle spaces in sheet names
Edge cases & warnings
- INDIRECT is volatile — recalculates every time, can slow workbooks
- Returns #REF! if the referenced sheet or range doesn't exist
- Cannot be used across workbooks (external references don't work)
- Google Sheets INDIRECT works the same way
Examples
"A1 = "Q1 Sales""
SUM('Q1 Sales'!B2:B100)"A1 = "Budget""
SUM('Budget'!B2:B100)Frequently asked questions
Why is INDIRECT considered bad practice?
INDIRECT is volatile (recalculates constantly), breaks when you rename sheets, and is hard to audit. Use direct references or structured Tables when possible. Use INDIRECT only when you truly need dynamic sheet references.
Can I use INDIRECT with named ranges?
Yes. =INDIRECT(A1) where A1 contains a named range name (like "SalesData") will reference that named range.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.