Have one simple formula that will reference the same cell or range of cells on multiple worksheets at once without having to name each worksheet separately.
(make sure to download our sample workbook so you can follow along)
Adding or Removing Sheets from the Workbook
Functions You can Use with a 3D Reference
Working with Sheets with Variable Names
I have a worksheet that consolidates sales data between stores and I want to have that data nicely presented on the Master worksheet like this:
To SUM the sales from the three worksheets Store 1, Store 2, Store 3, we use this formula in cell B4:
=SUM('Store 1:Store 3'!B1)
The formula in the example above is saying that you need to sum all of the data that is located in cell B1 in all of the sheets from Store 1 to Store 3.
Any sheet that is located between Store 1 and Store 3 will be included in this calculation, including the Store 1 and Store 3 sheets.
The syntax for this 3D Reference is like this:
=FUNCTION('Starting Sheet:Ending Sheet'!Cell or Range Reference)
The important thing about this type of reference is that ALL data must be located in the same place on all sheets that you want to include in the 3D Reference.
Every one of our store sheets looks like this:
There are a couple steps involved in creating the 3D Reference.
If you want, you can also copy this formula down just like you would any regular formula and it works the same way.
Now, we have 5 3D References that gather and sum the sales data for the first 5 weeks for three stores.
If you add or move any worksheets between Store 1 and Store 3 they will also be included in the calculation.
Similarly, if you delete sheets or simply move them outside of the bounds of Store 1 and Store 3 they will no longer be included in the calculation.
If you have a series of worksheet tabs, the names of which or order of which you will not always know, you can still use 3D References to perform calculations on them.
The trick is to create a starting and ending sheet and use these in the 3D Reference. Then, put all of the sheets that you want to be in the calculation between these two sheets. Make sure to leave the starting and ending sheets blank and it won't disrupt any of your calculations.
3D References are a handy little thing to use in Excel but they can be a little confusing at first. The main thing is that you must ensure that the worksheets included in the reference have the exact same layout and that all of the data in them is in the same place. This is easy to do when you export data from another program but it is not so easy to do if you are combining worksheets that other users have created (people tend to mess things up no matter how hard you try to prevent it).
To be honest, I don't use 3D References that much. The problem is that a lot of people won't understand what's going on if they need to debug or work on a worksheet that you have created using them and, if a user doesn't know that these references exist in a workbook or even what they are, they may change the order of the worksheets and mess-up the calculations. That said, I don't use these references when I am sending Excel workbooks out to other people.
Also, don't forget to download the accompanying spreadsheet so you can see examples of 3D References in Excel.