Excel INDIRECT Function: build a reference from text

Normally a formula points straight at a cell. INDIRECT takes the address as text and turns it into a reference. That lets a cell decide where the formula looks.

The formula

=INDIRECT(ref_text)

=INDIRECT("B5") is the same as =B5. The power comes when the text is built from other cells.

Use 1: same cell from many sheets

Sheets named Jan, Feb, Mar each have their total in B10. On a summary sheet, sheet names in column A. In B2:

=INDIRECT("'"&A2&"'!B10")

Copy down: each row pulls B10 from the sheet named in that row. The single quotes handle sheet names with spaces. Plain cross-sheet references: reference another sheet.

Use 2: dependent dropdowns

Name each list after its category (Fruit, Veg). In the second dropdown’s source: =INDIRECT(A2). Step by step: dependent drop-down list.

Use 3: a range that never shifts

=SUM(A1:A10) changes to A1:A11 if you insert a row. =SUM(INDIRECT("A1:A10")) always means exactly A1:A10.

Watch out

• #REF! — the text is not a valid address, or the sheet name is misspelled.
• Volatile — recalculates on every change; many INDIRECTs slow a big file.
• Closed files — INDIRECT to another workbook only works while that file is open.
• Renaming a sheet does not update the text, so the formula breaks.