Microsoft Office Tutorials and References
In Depth Information
Consolidating by category
Note
After you perform a consolidation, the references you type in the Consolidate dialog
box are retained when you save the workbook. The next time you open the workbook
and want to refresh the consolidated values, rather than entering the references again,
click Consolidate and click OK.
Figure 8-79 Range B5:I10 in the Consolidated worksheet now contains totals of the
corresponding cells in the 12 supporting worksheets.
Consolidating by category
Now let’s look at a more complex example. The Northwind Staff workbook contains
monthly sales totals for each salesperson, but each monthly worksheet has a few different
salespeople and a different number of salespeople, as shown in Figure 8-80.
You’ll find the NorthwindStaff.xlsx file with the other examples on the companion website.
The consolidation worksheet we’ll use for our example has columns for Units and Sales—
each worksheet is the same in this respect. When performing a consolidation by category,
your consolidation sheet cannot include row categories. The cells for these categories
(salespeople’s names) must start out blank on the consolidation sheet. Excel collects the
categories (names) and lists them as part of the consolidation process. The names are not
consistently arranged in the source worksheets, which is why we must use consolidation by
category rather than consolidation by position in this example.
Search JabSto ::




Custom Search