The INDIRECT function
The reference is built as text, then read: choose the sheet in a drop-down list, and the formula follows. Handy, fragile and slow.
Syntax
=INDIRECT(ref_text, [a1])
In a French Excel: =INDIRECT(réf_texte; [a1]).
Arguments
- ref_text: a reference as text, such as
"B4"orA1&"!B4". - a1: FALSE for an R1C1-style reference.
An example
=INDIRECT("'"&A1&"'!B4")
Cell B4 of the sheet whose name is in A1; the apostrophes protect names with spaces.
Pitfalls
- Volatile: recalculated on every change.
- Blind to changes: renaming a sheet or inserting a row does not update the text, and the formula breaks into #REF!.
- Another workbook must be open, otherwise #REF!.
Going further
- CHOOSE or INDEX, when the cases are known in advance.
- The formula audit: spots volatile functions.
Frequently asked questions
- How do I avoid INDIRECT to choose a sheet?
- Combine the sheets into one table with a "Month" column (Merge Excel files does it), then filter or use SUMIFS.