Page 212 - Excel Timesaving Techniques for Dummies

41_574272 ch36.qxd 10/1/04 10:54 PM Page 197
197
Linking Consolidated Data
9. Click the OK button to close the Consolidate determining which data to consolidate. To use the
dialog box and have Excel consolidate the top row of column labels, select the Top Row check
designated data. box in the Use Labels In section of the Consolidate
dialog box. To use the left column of row labels,
Figure 36-3 shows you the consolidated worksheet select the Left Column check box in this area. After
after closing the Consolidate worksheet and then you specify all the source areas (including the cells
increasing its window to full size. This worksheet that contain these column and row labels), click the
now contains the total sales for the last four years, OK button in the Consolidate dialog box to execute
2001 through 2004. the consolidation in the destination area.

Linking Consolidated Data

During a consolidation, Excel enables you to link the
data in the source areas specified in the All
References list box of the Consolidate dialog box to
the destination area in the new worksheet. That way,
any changes that you make to the values in the
source area will be updated automatically in the des-
tination area of the consolidation worksheet. To cre-
ate links between the source worksheets and the
destination worksheet, you simply select the Create
Links to Source Data check box in the Consolidate
dialog box prior to performing the consolidation.

• Figure 36-3: Full-size worksheet after consolidating the When you perform a consolidation with linking, Excel
sales data from the last four years. creates the links between the source areas and the
destination area by outlining the destination area.
Consolidating Data by Category (See Technique 35.) Each outline level created in the
destination area holds rows or columns that contain
the linking formulas to the consolidated data.
You consolidate data by category when the source
areas do not share the same cell coordinates in their Figure 36-4 shows just such an outline created during
respective worksheets but their data entries do use consolidation. I expanded the level of the outline
common row and/or column labels. When you con- showing the consolidation of the rock music CD sales.
solidate by category, you include these identifying
labels as part of the source areas. Unlike consoli- Here, you can see that during consolidation, Excel
dating by position, Excel copies the row labels created four detail rows for each of the four years of
and/or column labels you specify for use in the sales (2001, 2002, 2003, and 2004) used in the linked
consolidation. consolidation. These rows contain the external refer-
ence formulas that link to the source data. For exam-
When consolidating spreadsheet data by category, ple, the formula in cell B4 contains the following
you must specify whether to use the top row of col- formula:
umn labels and/or the left column of row labels in
=’[CG Media - 2001 Sales.xls]Sales01’!$B$4