Page 211 - Excel Timesaving Techniques for Dummies

41_574272 ch36.qxd 10/1/04 10:54 PM Page 196
196
Technique 36: Consolidating Data from Different Worksheets
If you’re consolidating the data in a new work- When you select the cell range by pointing, Excel
book, you need to open it (File➪New). If you’re minimizes the Consolidate dialog box to the
consolidating worksheets generated from a tem- Reference text box so that you can see what
plate, use the template to create the new work- you’re selecting. If the workbook is not visible in
book. (See Technique 1.) a window, choose it on the Window menu and
then select the cell selection as you normally
3. Select the cell at the beginning of the destina- would. (Remember that you can move the
tion area in the new worksheet or select the Consolidate dialog box — the one that’s been
cell range if you want to limit the destination minimized to the Reference text box — by drag-
area to a particular region.
ging its title bar.)
If you want Excel to expand the size of the desti- If the source worksheets are not open, click
nation area as needed to accommodate the the Browse button to select the filename in the
source areas, just select the first cell of this Browse dialog box to enter it (plus an exclama-
range.
tion point) into the Reference text box. Then you
4. Choose Data➪Consolidate to open the can type in the range name or cell references
Consolidate dialog box, shown in Figure 36-1. 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 characters when typ-
ing in the references for the source area.
7. Click the Add button to add this reference to
the first source area to the All References
list box.
8. Repeat Steps 6 and 7 until you add all the refer-
ences for all the source areas that you want to
consolidate, as shown in Figure 36-2.

• Figure 36-1: Using the Consolidate dialog box to total
2001-2004 sales.

5. (Optional) Select the function you want to use
in the Function drop-down list if you don’t
want the values in the source areas summed
together.
6. Select the cell range or type the cell references
for the first source area in the Reference
text box.
• Figure 36-2: The Consolidate dialog box after selecting all
the cell ranges to be totaled.