Overview
If you ask an Excel expert about the INDIRECT function, you’re likely to get one of two emotions: joy or fear! I get that there are pros and cons (we’ll cover those in a minute), but when used cautiously, it has been incredibly helpful for me. Below I’m sharing three examples from my time supporting retail/restaurant companies, where there is so much repetitive data (i.e. locations, regions, departments) that is often spread across several similarly-structured sheets. That’s often a good opportunity to use the INDIRECT function.
In this article, we’ll discuss how it works, the pros and cons, and three examples of when it saved me a lot of time. And two bonuses as we go – a couple of macros that help you save a ton of time using the INDIRECT function.
How it works
Fundamentally, INDIRECT lets you build a dynamic cell reference. Pieces of the cell reference – the file path, file name, sheet name, column, row, and cell – can be linked to other cells or the result of other functions (i.e. conditionals). There are times where you may want to choose part of the reference, such as a sheet or column, and INDIRECT is an easy option to offer that flexibility.
To see how it works, take a brief look at the image below. Cells C5 and C6 are referencing the same cell – cell C6 indirectly and cell C6 directly. Imagine needing to build a sheet full of references like this. Directly, that would take forever because almost every cell formula would need to be built separately. But with the indirect formula, you can link parts of that reference in a way that you can reuse that formula across and down!
It’s not just for one cell – you could indirectly reference a range of cells, an entire column, or an entire row. As for the file path and file name, just remember that INDIRECT is volatile, which means it will recalculate every time there is a change to the workbook, unlink direct references that won’t. In the case of the third example below, this isn’t always a bad thing.
One thing to keep in mind – if referencing a file, sheet, cell address or range name that doesn’t exist, INDIRECT returns a #REF! error. If referencing a file that isn’t open, you’ll also get the #REF! error. When indirectly referencing other files, often people will convert the reference to a direct reference (see the third example below for a macro on this!).
Pros and cons
Pros
The ability to dynamically reference cells is a huge benefit that leads to other benefits:
- You can reference cells across sheets or files (often helpful for summarizing or extracting values).
- You can sometimes avoid needing in-depth conditional formula logic.
- You can lock the cell references even if there are structural changes.
Cons
- It’s a volatile function – it recalculates each time there is a change in the workbook.
- It can sometimes seem overwhelming to build or read indirect references.
- It discourages best practices, such as keeping all your source data on one sheet in a tabular structure.
- It can allow you to bypass some security restrictions.
The 60-sheet P&L file
My first exposure to the INDIRECT function was at a restaurant company that operated about 60 different locations. Many of the template workbooks I inherited contained a sheet for each location, plus regional roll-ups and a consolidated roll-up (adding up to over 60 sheets!). The structure was identical – just different amounts. Most of the reports we could run from the general ledger exported this way also. It was extremely time-consuming thumbing through all the sheets to review amounts and not be able to see them side-by-side. After a little research, I realized I could use INDIRECT to easily reference all of these sheets with one fillable formula! And even better – it was dynamic so I could change the column or row I wanted to analyze!
See the formula in the screenshot below. This is very similar to the summary sheet I used back then. We wanted to see each of those sheets on a side-by-side basis for all the rows in those other sheets. The circled area is the column that was referenced. Change that cell and the column referenced for the entire sheet is updated in one step! Notice the INDIRECT function in the formula bar for cell C10 (the same formula used in all data cells).
I used a similar view to compare locations on a row-by-row basis where the row can dynamically change and display all the same columns. Instead of listed across the columns, all the sheets were labels on the left.
Bonus: To quickly build those two views in one step, I built a macro called Summarize Sheets. Just select the sheets you want to compare, run the Summarize Sheets macro, and voila, fancy sheets to analyze in seconds (compared to an hour or so building them manually)! Once those sheets are built, run the Summarize Sheets macro to easily navigate between them jumping to the related cell between the sheets!
The 20-sheet copy-paste file
One template I inherited was very interesting – it summarized about 20 different source data sheets that were part of a report run each month. They would spend a couple hours every month updating the formulas to reference the new data! Normally I would rebuild the entire file with best practices, reference a separate file, or build a macro to do all the updates. But I decided to do something a little different.
Having learned that INDIRECT doesn’t change with structure and just looks for a valid reference, I thought “Why not change those references to INDIRECT? That way I could delete those 20 source sheets, then copy/paste them again, then the formulas would all work again. It worked beautifully!
Now again, I don’t recommend this approach – there are generally much better approaches – but given the people who would be using the file and maybe take it over, this approach was easy and it worked. Check out the screenshot below – it’s a very basic example where the sheet name incorrect (same as if it was missing) then it’s corrected. The #REF! errors go away!
The 60-file copy-paste adventure
For another process I took over, I needed a ton of budget data points. They were spread across 60 separate store-level budget files, and 52 weekly sheets within each of these files. We needed about 30 data points from each sheet. That’s 93,600 data points! I asked the person who previously managed it how they updated it the last time, and they said it was a massive copy-paste exercise that took them all weekend. No way I was going to do that!
I built a list of all the data points we needed and what cell they were in, then replicated the list for each week, then for each store, with a pivot-unpivot trick I often use. Then I added an indirect reference for every single data point. As discussed above, those references don’t work when the file isn’t open. I converted the references from indirect to direct using the macro described in the next paragraph, and the formulas all updated. Boom – giant list of budget data we needed in one place! See the screenshot below for a very simple example of the approach I took.
Bonus: To make indirect references not volatile, I built the Remove Indirect macro. It does just what it says – for all selected cells, it replaces any INDIRECT function components with their direct reference equivalents.
Summary
As you can see, the INDIRECT function can be a really handy part of your Excel toolbox. In the first example above, it’s a great way to add a dynamic summary to a file full of worksheets. In the second example above, it’s a just one of many ways to update data (though I probably wouldn’t choose that approach if I was starting from scratch). In the third example above, INDIRECT was an awesome facilitator and the best part was how we could reuse that template the next time we needed to do a similar data refresh! Give it a try for yourself using one of the approaches above!
What has been your experience using the INDIRECT function? Would you do anything differently now that you know a little more about it? Share your thoughts in the comments below!
Recent Comments