Excel like a finance pro.

The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.

← All functions

INDIRECT

Lookups & references

Builds a reference out of text — which is how one summary tab reads twelve monthly sheets.

Difficulty

Advanced

Excel file

1What is it?

INDIRECT turns a piece of text into a real cell reference, so `=INDIRECT("'"&A5&"'!B12")` reads cell B12 from whichever sheet A5 names. Put the month names down column A and one formula pulls the same figure from every monthly tab. That is the legitimate use, and it is genuinely useful. Everything else about INDIRECT is a warning: it's volatile, so it recalculates on every change anywhere in the workbook; it breaks silently when a tab is renamed, because the text no longer points at anything; and it cannot read a closed workbook at all.

2What it looks like

INDIRECT(ref_text, [a1])

ref_text: Text that spells a reference — "B12", "Jan!B12", or "'Jan 2026'!B12" when the sheet name contains a space.
[a1]: TRUE (default) for A1 style, FALSE for R1C1. Rarely needed.

3When you use it

Pull the same cell from twelve monthly tabs into one summary column.
Let a dropdown choose which sheet a block of figures reads from.
Build a reference whose range depends on a count elsewhere.

4See it in action

Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.

The month name is data in a cell. Rename the tab and watch what happens.

=INDIRECT("'"&A4&"'!B12")Found the Mar sheet and read B12 from it.

	A	B	C
1	Sheet name	Pulled value	Result
2	Jan	41,000	Found the Mar sheet and read B12 from it.
3	Feb	38,500	The apostrophes cost nothing when unnecessary and save you when a name gains a space.
4		—	Volatile: every INDIRECT recalculates on every change anywhere in the workbook.

The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.

Third row names (A4)

5Common errors

#REF!The text doesn't name anything that exists — usually a renamed tab, or a sheet name with a space and no apostrophes.

Fix: Wrap sheet names in apostrophes ALWAYS, as above. It costs nothing when unnecessary and saves you when the name gains a space.

#REF! on a closed workbookINDIRECT cannot read a file that isn't open. Unlike a normal link, there's no cached value.

Fix: Open the source, or use a real link, or pull the data in properly with Power Query.

The workbook has become slowINDIRECT is volatile — every one recalculates on every change anywhere.

Fix: A handful is fine. A column of two thousand will make the file miserable; restructure with a proper consolidation instead.

Renaming a tab broke the summary silentlyText references aren't updated by a rename the way real references are.

Fix: This is the fundamental trade. If the tabs get renamed, don't use INDIRECT.

6Better functions & alternatives

A real reference — Always preferable when you can write one — it survives renames and doesn't recalculate constantly.
Power Query — The right answer for combining twelve sheets. It's built for exactly this and doesn't go stale or volatile.
CHOOSE — When the set of sheets is small and fixed, CHOOSE over real references is safer.

Want INDIRECT already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.