Microsoft Office Tutorials and References
In Depth Information
Consolidating by position
3. Select the source range in the first worksheet using the mouse. In this example, we
selected B5:I10 in the Jan worksheet.
Click Add in the Consolidate dialog box. Excel transfers the reference from the
Reference text box to the All References list. Repeat for each worksheet you want to
Be sure all supporting workbooks are open when you are building your
consolidation worksheet to make it easier to type references. (If a workbook is closed, you must
manually type references to it, and you really don’t want that.) After you have the
consolidation set up and save the workbook, supporting workbooks can stay closed during
future consolidations. If you do have to type a reference, you must use the form [File
Name]Sheetname!Reference. If the reference is in the same workbook, the file name
(and its surrounding brackets) is unnecessary. If you assigned the source range a name,
you can use this name in place of Reference (highly recommended). For more
information, see “Naming cells and cell ranges” in Chapter 12.
After you add the first range—$B$5:$I$10 in the Jan worksheet—Excel selects the same
range in each worksheet when you click its tab. Just click a worksheet tab, and then click
Add to add references. Figure 8-78 shows the completed dialog box. Figure 8-79 shows the
Figure 8-78 The Consolidate command uses the references in the All References list to create
the consolidated totals.