INDIRECT Function: Build Dynamic References From Text

INDIRECT never reads a cell's value first - it reads a text string and treats that text as an address. So =INDIRECT("B2") is a two-step formula: Excel resolves the text "B2" into the reference B2, and only then returns what that cell holds. Because the address arrives as text, you can build it out of other cells, and the reference then follows those cells as they change. That is the whole idea behind a dynamic reference.

The most common use is a summary sheet that pulls one number per tab. Put the tab names down a column, join each name to the target address with the ampersand, and hand the string to INDIRECT. Dragging the formula down then reads a different tab on every row - no three-dimensional formulas and no manual edits each month.

You can also build the end of a range from a cell, which gives you a SUM that grows without being edited. Keep the last row number in its own cell, concatenate it into a range string such as B2:B13, and wrap the whole thing in SUM. Add rows, bump the number, and the total follows.

Named ranges plus INDIRECT are the classic way to build dependent dropdowns. Create a defined name for each list, then point Data Validation at =INDIRECT(A1). When someone picks a list name from the first dropdown, the second dropdown is rebuilt from that name - the selection in a cell literally becomes the address.

INDIRECT is volatile: it recalculates on every workbook change, so thousands of them will visibly slow a model. It also does not follow data when rows or columns are inserted - the text keeps pointing at the same physical cell. And a text address is much harder to audit than a real reference, because nothing on screen shows where it lands. For most jobs INDEX, MATCH and OFFSET do the same work with fewer surprises, and INDEX is not volatile at all.