Excel INDIRECT Function: Dynamic Cell References
INDIRECT Function is a Excel function that indirect builds a sheet reference from the text in a1. Formula Genius generates and validates this formula automatically from a plain-English prompt.
INDIRECT converts text strings into cell references. Use it to dynamically reference sheets, create flexible formulas, and build cascading dropdowns.
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 any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.