Lookup & Reference
INDIRECT function: turn text cell and range strings into live references
Evaluate cell or range addresses stored as text with INDIRECT so summary formulas can switch target columns dynamically.
Switch which range SUM reads from a selector cell
Normally, typing "B2:B5" inside a cell stores plain text, and SUM("B2:B5") fails because it sees a string rather than cells. INDIRECT(ref_text) resolves that text string into a live cell or range reference at calculation time.
Storing "B2:B5" (Plan) or "C2:C5" (Actual) in selector cell E2 lets =SUM(INDIRECT(E2)) total whichever column range E2 names.
| Quarter (A) | Plan (B) | Actual (C) | Selector E2 | SUM(INDIRECT(E2)) |
|---|---|---|---|---|
| Q1 | 120 | 135 | B2:B5 | 540 (Plan total) |
| Q2 | 130 | 140 | C2:C5 | 580 (Actual total) |
| Q3 | 140 | 150 | C4 | 150 (single cell) |
| Q4 | 150 | 155 | (blank) | "Choose range" with IF guard |
Dynamic range total and fixed-row behavior
Because "B2:B5" is plain text until INDIRECT evaluates it, inserting or moving rows will not rewrite the string automatically—useful when you intentionally want to lock "A1:A10", risky if you expect references to shift with structural edits.
=SUM(INDIRECT(E2))Guard empty or unvalidated address inputs
If E2 is empty, INDIRECT cannot resolve a valid cell address. Checking E2="" before calling INDIRECT avoids raw error codes in dashboards.
=IF(E2="", "Choose range", SUM(INDIRECT(E2)))Try the example
Use the original values or change them to test your understanding.