INDIRECT
Turn text such as "B3" or "B2:B4" into a live reference, so the address a formula reads can be calculated.
Syntax
INDIRECT(ref_text, [a1])| Argument | Required | Description |
|---|---|---|
ref_text | Required | An A1-style address as text — a cell or a range on the same sheet. |
a1 | Optional | TRUE or omitted for A1 style. R1C1 style is not supported yet. |
Examples
Every example below was executed against VisiGrid engine 0.54.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=INDIRECT("B3") | 30 | |
=INDIRECT(D2) | 30 | The address is read from a cell. |
=SUM(INDIRECT("B2:B"&4)) | 60 | A range whose end is calculated. |
=INDIRECT("nope") | #REF! | Text that is not an address. |
When the address itself is data
INDIRECT is for formulas whose target is decided by a value: a month name
picking a column, a user typing which cell to read, a row number calculated
elsewhere.
=INDIRECT("B"&ROWS(B2:B3)+1) → 30Why to avoid it where you can
A reference built from text is invisible to everything that understands
references. Insert a row above B3 and =B3 becomes =B4, but
INDIRECT("B3") keeps reading B3 — now a different cell. Tracing precedents
cannot see through it, and because the engine cannot tell in advance which cells
it reads, formulas containing it are recalculated conservatively rather than
only when their inputs change. When the choice is
by position, INDEX does the same job with a real range
and none of those problems.
Same sheet only
INDIRECT currently resolves addresses on the sheet the formula is on.
"Sheet2!A1" returns #REF!, as does R1C1 text such as "R3C2".
Excel compatibility
Matches Excel for A1-style references on the same sheet. References to another sheet ("Sheet2!A1") and R1C1 style return #REF! here; Excel supports both.