Chapter 27: Linking and Consolidating Worksheets
601
l (^) Use Labels In check boxes: Use to instruct Excel to perform the consolidation by exam-
ining the labels in the top row, the left column, or both positions. Use these options when
you consolidate by category.
l Create Links to Source Data check box: When you select this option, Excel adds sum-
mary formulas for each label and creates an outline. If you don’t select this option, the
consolidation doesn’t use formulas, and an outline isn’t created.
l (^) Browse button: Click to display a dialog box that enables you to select a workbook to
open. It inserts the filename in the Reference box, but you have to supply the range refer-
ence. You’ll find that your job is much easier if all the workbooks to be consolidated are
open.
l (^) Add button: Click to add the reference in the Reference box to the All References list.
Make sure that you click this button after you specify each range.
l (^) Delete button: Click to delete the selected reference from the All References list.
A workbook consolidation example
The simple example in this section demonstrates the power of the Data Consolidate feature. Figure 27.7
shows three single-sheet workbooks that will be consolidated. These worksheets report product
sales for three months. Notice, however, that they don’t all report on the same products. In addi-
tion, the products aren’t even listed in the same order. In other words, these worksheets aren’t laid
out identically. Creating consolidation formulas manually would be a very tedious task.FIGURE 27.7Three worksheets to be consolidated.