3-D Reference Style

We have seen relative references and absolute references in one of our previous posts. We also talked about R1C1 style of referencing. I said we will see more referencing style in a future post. This post is about that. What I am talking about is 3D referencing style. Sounds interesting? Well, it is. Let’s see about it.

What is 3D reference?

Simply put, a cell reference that is spread over multiple sheets is called 3D reference. This is particularly useful to use in formulas when you have to retrieve contents of cell(s) from across similar sheets. To understand better, let’s go by a simple example.

Assume a situation where you maintain your daywise sales total in a sheet. You name the sheet by the month name i.e. Jan, Feb, Mar etc; this sheet has all dates in column A and column B contains sales total for that day. In the next month, you roll over this sheet with same format. Now, for your trend analysis, you wanted to create a summary sheet that gives you day wise sales of all the months together. That means sales made on 1^st day across 12 months in one cell and so on. What will you do? Think of it. To show a visual representation of what I mean, I copied the pictures here. (I took only up to 8^th day for want of space; notice that cell references containing the sales are consistent across sheets.

Jan Tab

Feb Tab

March tab

Summary Tab

Here is what I used to do. I basically enter a formula in B2. Below are the steps I follow.

1. Type “=” (equal to) in cell B2 to start a formula
2. Click on January related sheet i.e. tab named Jan and select related cell in column B that contains sales total
3. Type a “+” (plus)
4. Click on February related sheet i.e. tab name Feb and select related cell in column B that contains sales total
5. Repeat steps 2 to 4 till the end of all the months
6. Press Enter and I will get required total in B2. Now the formula looks like “=Jan!B2+Feb!B2+Mar!B2+……+Dec!B2”
7. Drag this formula until the end of all days.

We have to perform around 30 steps in order to get data from 12 months. You can imagine the practical situations of 1 sheet for each day or multiple sheets to be considered. Pretty time consuming, huh?

You got the idea. 3D reference might help? Yes boss, that’s why I gave such a long backdrop. Let’s see what you can do to simplify the above task.

1.  Type “=SUM(Jan:Dec!B2)” in cell of summary sheet
2. Drag the formula till the end of dates.

That’s it. What you did is, you created a 3D reference of cell B1 spanning all the 12 sheets. Look at the below picture. Cell B2 contains old method and D2 contains 3D reference. Result is same in both the formulas. (I only calculated until 3^rd sheet which is Mar, you can extend the same way to Dec)

Understood the power of 3D references? Instead of typing the sheet names in the formula, you can also type equal to sign and click on first tab you want to start referencing then hold down Shift key and click on last tab you want to reference. Now click on cell(s) you want to include and press Enter.

What’s more, if you add a new sheet in between the given 3D reference sheets, formula will automatically consider that sheet too. You can also use 3D references in Named Ranges. Just follow the steps in above paragraph in the Refers To: field of named range.

How 3D Reference Adjust when Sheets are Added, Deleted or Moved

Continuing from our example above, we have the formula =SUM(Jan:Mar!B1). Let’s see how this reference moves when the sheets are rearranged.

Insert New Sheet: If you insert a new sheet anywhere between Jan and Mar sheets, 3D reference automatically includes new sheet in calculation. No change in formula if you insert new sheet before or after Jan-Mar sheets.

Delete Sheet: If you delete an existing sheet between Jan-Mar, 3D reference will no more consider that sheet values in calculation. Obviously, no change if you delete a sheet outside the referenced sheets.

Move Sheet: If you move a sheet which is in between Jan-Mar to a place outside Jan-Mar sheets, 3D reference will no longer consider those values in calculation. No change if you move sheets those are outside of referenced sheets.

Delete Start Reference or End Reference Sheet: In this case, 3D reference will be adjusted removing that deleted point. In our example, if you delete sheet named Jan, formula automatically becomes =SUM(Feb:Mar!B2)

Move Start Reference or End Reference Sheet: If you move end points to a new place, 3D reference adjusts to consider the new sheets that are between the new placing of end points. In our example, if you move Jan sheet to the right of Feb sheet, formula now only considers only Jan & Mar.

There is one limitation of 3D references. You can only use it with functions SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, VARPA.

You can download the workbook I used to present this example by clicking here.

I use 3D reference extensively while preparing summary reports of different tabs. But I will be careful to make sure that the same cell(s) across the sheets contain the data I need. Hope you find this feature useful in your office too. Happy Learning!!