I stopped consolidating sheets by hand because Excel’s INDIRECT function does all the work for me

Sometimes, you need to pull data from multiple sheets and analyze it in a summary sheet or dashboard. You can try copying and pasting what you need into one sheet and manually tweaking it, but it's a time-consuming process. Instead of doing it by hand, you can use the INDIRECT function to save yourself some time and effort.

With the INDIRECT function, you can reference and consolidate key data points across sheets. It's a straightforward process that requires a single formula that can easily be applied to multiple cells. Furthermore, if the source sheet gets renamed, nothing breaks, and everything is updated accordingly.

The problem with consolidating sheets manually

Formulas can easily break in certain scenarios

Say there is a workbook with three sheets named North, South, and East, each containing sales data for various products. You can create a summary sheet to compare and aggregate sales totals using the SUM function. If the sales are in column D, the first formula I'd write for the North sheet will be:

        =SUM(North!D:D)

    

To get the total sales for the South and East sheets, you would need to manually enter the formulas for them in the cells below. One big problem is that it can be tedious when there are many sheets. It's also easy to make a mistake, leading to the #REF! error.

Furthermore, if the sheet name changes, the formula breaks. If you hard-coded the sheet name in multiple places in your summary, you must find and replace each instance, which is tedious and error-prone.

The INDIRECT function in a nutshell

The key to building dynamic cell references

If you don't know what the INDIRECT function is, its primary function is to convert a text string into a cell reference. I will quickly run through what you need to know to make the example make sense.

Suppose you have the value 5 in cell A1 and the value A1 in cell B1; the following formula will return the value 5.

        =INDIRECT(B1)

    

What's happening here is that the INDIRECT function has converted the value in B1 into a cell reference (A1). It then returns the value in the referenced cell, which is 5.

A close analogy is looking up an address, entering the coordinates into a GPS, and then driving there to retrieve a package. It sounds like a long way of going about it, but it helps when building dynamic cell references.

Continuing with our example, suppose we replace the value in B1 with 1 (keep in mind that this will be a text value until you convert it into another format). We can use string concatenation to build a cell reference.

        =INDIRECT("A" & B1)

    

Here, the INDIRECT function will take the string "A" and combine it with the value in B1 using the ampersand (&) to form the cell reference A1. That is how we still end up with the value 5. What's even better is that if I change the value in B1 to 2, the function will now look to cell A2, and so on.

Summarizing an Excel sheet with the INDIRECT function

Where the function truly shines

Now, let's go back to our sales data example. We first need to create a helper column with the names of all our sheets. Let's assume that the range is A2:A4 for North, South, and East. Since A2 contains North, we can sum the sales using the SUM and INDIRECT functions by entering the following formula in B2:

        =SUM(INDIRECT("'" & A2 & "'!D:D"))

    

The values in the INDIRECT formula ("'" & A2 & "'!D:D") build the string 'North'!D:D. This serves as a real reference to column B on the North sheet. Keep in mind that the single quotes are a safeguard for sheets with spaces or special characters. Otherwise, they aren't really necessary.

INDIRECT cannot reference closed workbooks because it needs the workbook to be open so it can find what has been referenced. Otherwise, it will return a #REF! error. It can also not construct 3D references.

The beauty of this approach is that you can copy this formula down to B3 and B4, and it will automatically reference the South and East sheets, respectively. The A2 in the formula will become A3 and A4 when you do this. So, instead of manually entering the formula in each cell, this saves you a lot of time and is highly accurate.

Furthermore, if you rename a sheet, you only need to update the name in column A. This will update all cell references, and the formula continues to work perfectly. There's no need to hunt down every cell where you entered it.

Also, you can use the IFERROR function for some error handling to account for situations where the INDIRECT function will return the #REF! error.

=IFERROR(SUM(INDIRECT("'" & A2 & "'!D:D")), "")

Some caveats to be aware of

It’s a volatile function

INDIRECT is a volatile function, meaning it recalculates every time any cell in your worksheet changes. Volatile functions can make your spreadsheet lag significantly, especially in moderate-to-large workbooks.

In many scenarios, you'd want to limit the usage of the INDIRECT function to where it's necessary. However, for light summary sheets with low input ranges, the performance hit can be negligible.

Optimize how you consolidate your sheets with formulas

When creating summary sheets and dashboards, the INDIRECT function is a game-changer. It can get even more dynamic with some of Excel's other features. For instance, you can use Excel dropdown lists with the function to switch sheets on the fly. Welcome to an arguably better way of consolidating sheets in your workbook.