Page 283 - Excel 2013 All-in-One For Dummies

Consolidating Worksheets 265
in the range name or cell references you want to use. If you prefer, you can type in the entire cell reference including the filename. Remember that you can use the asterisk (*) and question mark (?) wildcard charac- ters when typing in the references for the source area.
7. Click the Add command button to add this reference to the first source area to the All References list box.
8. Repeat Steps 6 and 7 until you have added all the references for all the source areas that you want to consolidate.
9. Click the OK button in the Consolidate dialog box.
Excel closes the Consolidate dialog box and then consolidates all the values in the source areas in the place in the active worksheet desig- nated as the destination area. Note that you can click the Undo button on the Quick Access toolbar or press Ctrl+Z to undo the effects of a con- solidation if you find that you defined the destination and/or the source areas incorrectly.
Figure 4-13:
Using the Consolidate dialog box to total sales data for three years stored on separate worksheets.
Figure 4-14 shows you the first part of a consolidation for three years (2010, 2011,and2012)ofrecordstoresalesinthenewlycreatedCG Media 2010 - 12 Consolidated Sales.xlsx file in the workbook window in the upper- left corner. The Consolidated worksheet in this file totals the source area B4:R21 from the Sales worksheets in the CG Media - 2010 Sales.xlsx workbook with the 2010 annual sales, the CG Media - 2011 Sales.xlsx workbook with the 2011 annual sales, and the CG Media - 2012 Sales. xlsx workbook with the 2012 annual sales. These sales figures are consoli- dated in the destination area, B4:R21, in the Consolidated sheet in the CG Media 2010 - 12 Consolidated Sales.xls workbook. (However, because all these worksheets use the same layout, only cell B4, the first cell in this range, was designated at the destination area.)
Book II Chapter 4
Managing Worksheets