71 - Indirect
I love Excel's Indirect function. It turns a text string (e.g. in another cell) into an actual cell reference.
This lets you e.g. combine results from multiple dropdowns into one cell reference. You can do some fancy lookups.
Indirect is unfortunately a volatile function, which means it recalculates every single time you change the value of any cell. This is because the cell can't know ahead of time if its reference has been changed, without actually checking it.
Meanwhile, if cell A3 is equal to cell A2, then A3 will only recalculate if A2 is changed.
If you've ever had a workbook that was slow to update, chances are it's full of volatile functions. These include Now and Today, Indirect and Offset, but also conditional formatting.
I would only really use Indirect if I need to do a lookup into different sheets, e.g. if your data is split into sheets based on months or salespeople rather than being combined into one table.
00:00 Basic example
01:01 Volatile functions
02:38 Turn off calculations
02:59 Using Indirect for lookups
04:45 R1C1 style
05:52 A normal Index Match Match
06:18 Using Indirect to split up a lookup
08:06 Lookup in variable sheets
11:34 Lookup in variable workbooks