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

If you want Excel to use a particular range in the worksheet for all consolida- tions you perform in a worksheet, assign the range name Consolidate_Area to this cell range. Excel then consolidates data into this range whenever you use the Consolidate command.
When consolidating data, you can select data in sheets in workbooks that you’ve opened in Excel or in sheets in unopened workbooks stored on disk. The cells that you specify for consolidation are referred to as the source area, and the worksheets that contain the source areas are known as the source worksheets.
If the source worksheets are open in Excel, you can specify the references of the source areas by pointing to the cell references (even when the Consolidate dialog box is open, Excel will allow you to activate different worksheets and scroll through them as you select the cell references for the source area). If the source worksheets are not open in Excel, you must type in the cell references as external references, following the same guidelines you use when typing a linking formula with an external reference (except that you don’t type =). For example, to specify the data in range B4:R21 on Sheet1 in a workbook named CG Media - 2012 Sales.xlsx as a source area, you enter the following external reference:
‘[CG Media – 2012 Sales.xlsx]Sheet1’!$b$4:$r$21
Note that if you want to consolidate the same data range in all the worksheets that use a similar filename (for example, CG Media - 2010 Sales, CG Media - 2011 Sales, CG Media - 2012 Sales, and so on), you can use the asterisk (*) or the question mark (?) as wildcard characters to stand for missing characters as in
‘[CG Media - 20?? Sales.xlsx]Sheet1’!$B$4:$R$21
In this example, Excel consolidates the range A2:R21 in Sheet1 of all versions of the workbooks that use “CG - Media - 20” in the main file when this name is followed by another two characters (be they 10, 11, 12, 13, and so on).
When you consolidate data, Excel uses only the cells in the source areas that contain values. If the cells contain formulas, Excel uses their calculated values, but if the cells contain text, Excel ignores them and treats them as though they were blank (except in the case of category labels when you’re consolidating your data by category as described later in this chapter).
Consolidating by position
You consolidate worksheets by position when they use the same layout (such as those created from a template). When you consolidate data by position, Excel does not copy the labels from the source areas to the destination area, only values. To consolidate worksheets by position, you follow these steps:
Consolidating Worksheets 263
Book II Chapter 4
Managing Worksheets