The Excel INDIRECT function converts a text string like "B4" or "North!B9" into a real, live cell reference. That lets you assemble references from cell values — pick a sheet name from a dropdown and pull its total, for example. The trade-offs: INDIRECT is volatile, it returns #REF! the moment the text doesn’t name a valid reference, and it cannot read from closed workbooks.
North), pull cell B9 from that sheet with:
"North!B9" as a real reference. Change A2 to South and the formula instantly reads from the South sheet.
Syntax
| Argument | Description | |
|---|---|---|
ref_text | Required | Text that describes a reference: a cell ("B4"), a range ("B2:B9"), a named range, or a sheet-qualified reference ("North!B9"). Usually assembled with &. |
a1 | Optional | TRUE or omitted = interpret ref_text in A1 style. FALSE = interpret it in R1C1 style ("R4C2" = row 4, column 2). |
INDIRECT is volatile. Like OFFSET, it recalculates on every worksheet change. Used sparingly that’s fine; sheets full of INDIRECTs get slow. Excel also can’t adjust the text when you rename sheets or insert rows — the string stays whatever you wrote.
Worked examples: from plain text to sheet switching
The simplest case: a cell holds the address of another cell, and INDIRECT follows it.
The classic real-world pattern is a sheet-name switcher: one summary tab that pulls the same cell from many identically laid-out sheets:
| A | B | C | |
|---|---|---|---|
| 1 | Region | Cell with total | Formula |
| 2 | North | North!B9 | =INDIRECT(A2&"!B9") |
| 3 | South | South!B9 | =INDIRECT(A3&"!B9") |
| 4 | East | East!B9 | =INDIRECT(A4&"!B9") |
Sheet names with spaces need apostrophes: =INDIRECT("'" & A2 & "'!B9"). If a sheet is called North Region, the reference must read 'North Region'!B9, so wrap the name in single quotes inside the text.
INDIRECT also accepts named ranges as text — the backbone of dependent dropdown lists: name each list of options after its category, then point Data Validation at =INDIRECT(A2).
Try it: build a reference from text
Assemble ref_text piece by piece. Pick a sheet (as if cell A2 held its name) and a cell, and watch INDIRECT resolve the text into a value.
Result:
Limitations: closed workbooks, volatility, fragility
Three limitations catch almost everyone:
1. Closed workbooks return #REF!. =INDIRECT("[Budget.xlsx]Sheet1!B9") works only while Budget.xlsx is open. The moment it closes, every INDIRECT pointing at it shows #REF!. Regular external links keep their last value; INDIRECT does not.
2. Text never auto-adjusts. Rename a sheet, insert a row, move a cell — normal references update, INDIRECT’s strings don’t. That can be the point (a reference that refuses to move), but it’s also how silent breakage happens.
3. Volatility. Every INDIRECT recalculates on every change, plus everything downstream of it.
Alternatives: INDEX with MATCH for position-based lookups, CHOOSE to switch between a fixed set of ranges, and XLOOKUP for value-based lookups. Reach for INDIRECT only when the reference genuinely must come from text.
Errors & common pitfalls
#REF! — ref_text isn’t a valid reference. A typo in the assembled string, a sheet that doesn’t exist, a deleted named range, or a referenced workbook that is closed. Test by putting the assembled text in a helper cell to eyeball it.
#NAME? — missing quotes. =INDIRECT(B4) reads the text inside B4; =INDIRECT("B4") reads B4 itself. Forgetting quotes around a literal address (or quoting a cell reference you meant to use) flips the meaning.
Pitfall: sheet names with spaces. "Q1 Sales!B9" fails — it must be "'Q1 Sales'!B9". Build the apostrophes into the string.
Pitfall: R1C1 surprises. With a1 set to FALSE, "R4C2" means B4. Mixing styles — passing A1-style text while a1 is FALSE — returns #REF!.
Practice workbook
Frequently asked questions
Why does INDIRECT return #REF!?
Can INDIRECT reference another workbook?
=INDIRECT("[Budget.xlsx]Jan!B9") returns #REF! as soon as Budget.xlsx closes. For closed files use a normal external link, Power Query, or restructure so the data lives in one file.How do I reference a sheet whose name has spaces?
=INDIRECT("'" & A2 & "'!B9"). The resolved string must look like 'Q1 Sales'!B9.Is INDIRECT volatile?
What is the a1 argument for?
ref_text. TRUE or omitted = A1 style ("B4"). FALSE = R1C1 style ("R4C2" = row 4, column 2), which is handy when computing row/column numbers: =INDIRECT("R" & D1 & "C2", FALSE).How do INDIRECT dependent dropdowns work?
Fruit and Veg). If A2 holds the chosen category, set the second dropdown’s Data Validation source to =INDIRECT(A2) — it resolves the category text into the matching named range.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class