INDIRECT turns a piece of text into a real cell or range reference. This lets you build references from other cells.
Syntax
=INDIRECT(ref_text, [a1])
Basic example
If A1 contains the text B5, then =INDIRECT(A1) returns the value in cell B5.
Pull data from a sheet chosen by name
If B1 contains a sheet name such as January:
=INDIRECT("'"&B1&"'!C10")
Change B1 to February and the formula reads C10 from the February sheet.
Dependent drop-down lists
Name each sub-list after its parent item, then use =INDIRECT(B2) as the Data Validation source of the second list. It changes with the first selection.
Sum a range chosen by name
=SUM(INDIRECT("Sales_"&B1)) sums a named range whose name depends on B1.
Drawbacks
- INDIRECT is volatile: it recalculates all the time and can slow large files.
- It does not work if the other workbook is closed.
- References break silently if a sheet is renamed.
Use it where it truly saves effort, and prefer INDEX where you can.
Worked example
Sheet names are in A2. =SUM(INDIRECT("'"&A2&"'!B2:B10")) sums B2:B10 on the sheet named in A2. For a cell reference in text: =INDIRECT("B"&5) returns B5.
Common mistakes
- Using it on closed workbooks. It returns #REF!.
- Overusing it, which slows large files because it is volatile.
- Missing quotes around sheet names with spaces.
Frequently asked questions
Why is INDIRECT slow?
It is volatile, so it recalculates with every change.
Is there an alternative?
CHOOSE, INDEX or a named range are often better.
Where is it useful?
Dependent drop-downs and picking a sheet by name.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: INDIRECT, ADDRESS, OFFSET.
Start Excel practice → Case studies
