3D Cell References
You are currently viewing a sample of the Cram Kit. Click here to unlock everything.
Imagine we had the following month-to-month financial review in Excel...
...and wanted to create a "year-in-review" sheet. To do so, we can use 3D cell references!
3D cell references enable you to reference the same individual cell or range from multiple different worksheets.
Coding a 3D Cell Reference
The first step to 3D referencing is to take note of all the worksheets that you'll be referencing, not cells.
In our case, we'll be referencing the August, September, October, November, and December sheets.
Next, let's move to our Year in Review sheet and pick the first cell we'll 3D reference. We'll start with our "Rent" category.
Without 3D referencing, if we wanted to compute the sum of all rent expenses for August through December, it'd look like this:
=August!B1 + September!B1 + October!B1 + November!B1 + December!B1
But this gets annoying, and imagine if you had more months. Ugh. Even more typing.
With a 3D cell reference, we can SUM these values across all sheets with ease!
=SUM(August:December!B1)
You can also do this like so (while holding shift after clicking on the August worksheet):
What this does is calculate the sum of all the "B1" cells within the boundaries of the August and December sheets.
One very important thing to note though, is that the structure of each of those sheets from August to December must be the same.
But... why?
Say in September, we accidentally placed our "Grocery" charge in cell B1.
Our 3D cell reference is not smart enough to notice that September's B1 is not rent, but grocery, and shouldn't be included in the SUM calculation. It would just add September's grocery charge to the sum instead of September's rent charge. This would cause our Year-in-Review sheet to have an incorrect total rent charge.
When using 3D cell references, you must ensure the structure of each of the sheets is the same.
To complete the rest of our Year-in-Review sheet, all we need to do is copy down our formula to the other categories like so:
How does this work? Because the 3D cell reference changes to B2, B3, B4, etc. as you copy it down each cell. It's still a relative cell reference!
To convert this relative cell reference to an absolute cell reference, we only need to add the "$" in front of the row/column value of B2. If you wanted to use an absolute cell reference instead, you could do it like so.
=SUM(August:December!$B$1)
Now when we copy it down to the other categories, each one has the sum of all rent charges. This is because the 3D cell reference stays at "B2" for each one!
Note: there's no reason you'd want your rent charges to be copied into your grocery, entertainment, etc. The point of this divergence was to show you that 3D cell references work with absolute cell references as well as relative cell references.
Using a 3D cell reference with a range
Say we want to compute the total charges incurred over the year in our Year in Review sheet.
To do so, we can 3D reference a range instead of a single cell, like so:
=SUM(August:December!B1:B5)
This sums the range of "B1:B5" in each of the sheets between August and December, resulting in a total charge of $11,677.00.
Note: Yes, you could've just summed all the values in the Year in Review sheet. This example was to show the functionality of working with ranges in 3D cell references.