Three Dimensional Formulas are, by far, the most flexible of the three methods of data consolidation.
With Three Dimensional Formulas, the placement of the data you are summarizing, or consolidating, has no bearing on the final outcome. Moreover, it doesn’t matter in which worksheet or workbook file the detail data is stored.
And, you probably already know how to create formulas. Creating a Three Dimensional Formula is as simple as being able to point and click on the data you want to summarize.
Let’s look at an example.
I have a workbook that tracks the sales of Widgets, Ball Bearings, and Hammers for each of four Regions (East, West, North & South). I already have the detail data by Region for each of the three products broken down by quarter. Each product already has their own worksheet tab (see sample below).
Here’s what my formula might look like in Excel:
In other words, on my Summary tab, the above formula would total the sum for cell B6 on the Widgets worksheet, cell B6 on the Ball Bearings Worksheet and cell B6 on the Hammers worksheet. The reason that the worksheet name “Ball Bearings” is surrounding by apostrophes is that there is a space in the worksheet name.