A few weeks ago, I found myself looking through a spreadsheet and saw a function named indirect nested within a larger function. You can imagine my reaction:
After learning more about the it, I’ve been working to get more comfortable with indirect so that I can build it into my own models. I’d like to share the magic with all of my readers here :)
In short, =indirect(B3) lets you point to a value in another cell, and use that as a reference itself. I’ve attached a visual example below; when I hit enter, cell B4 will look to cell B3, which will grab the value of B2, thus referencing the value “$12” into B4:
Not only can indirect help me reference another cell, it can also be used to reference another sheet, a named range, etc. Additionally, indirect can concatenate multiple cells or values together to create a large dynamic reference.
For example, if we receive a massive Excel report of employee sales, with each employee on a separate tab, we can build a table to dynamically pull the sales of each employee based on the name of the tab:
When would I use this function?
Imagine that you need to build a model that crunches data from a recurring report, and compares it to data from the past iteration of the report. The indirect function may be the cleanest way to scale this, since you could simply import the fresh data into a new tab, adjust the cell that references the freshest data (or build a formula that looks for the appropriate tab name), and easily recalculate.
I’d love to hear your thoughts in the comments below — and I look forward to sharing my next learnings in a fresh post!