Microsoft Office Tutorials and References
In Depth Information
Consolidating Data by Linking Workbooks
Consolidating Data by Linking Workbooks
You also can consolidate
data across different
workbooks using the
Consolidate button on
the Data tab on the
Ribbon, rather than by
entering formulas. For
more information on
the Consolidate but-
ton, type consolidate
in the Search box in
the Excel Help dialog
box, and then click the
‘Consolidate data in mul-
tiple worksheets’ link in
the Results list.
Earlier in this chapter, the data from three worksheets were consolidated into another
worksheet in the same workbook using 3-D references. An alternative to this method is
to consolidate data from worksheets in other workbooks. Consolidating data from other
workbooks also is referred to as linking. A link is a reference to a cell or range of cells in
another workbook. In this case, the 3-D reference also includes a workbook name. For
example, the following 3-D reference pertains to cell B5 on the Kansas City sheet in the
workbook NDVR Kansas City Profi t Potential located on drive E.
‘E:\[NDVR Kansas City Profi t Potential.xls]Kansas City’!B5
location workbook name sheet name cell reference
The single quotation marks surrounding the location, workbook name, and sheet
name are required if any of the three names contain spaces. If the workbook you are refer-
ring to is in the same folder as the active workbook, the location (E:\) is not necessary.
The brackets surrounding the workbook name are required.
To illustrate linking cells between workbooks, the Company, Louisville, Kansas
City, and Portland worksheets from the workbook created earlier in this chapter are on
the Data Files for Students in separate workbooks as described in Table 6–10. In the
workbook names in Table 6–10, the NDVR stands for NextDVR. The region workbooks
contain the region data, but the NDVR Company workbook does not include any consol-
idated data. The consolidation of data from the three region workbooks into the NDVR
Company Profi t Potential workbook will be completed later in this section.
Table 6–10 Workbook Names
Worksheet in NextDVR Profi t Potential
Saved on The Data Files for Students
Using the Workbook Name
NDVR Company Profi t Potential
NDVR Louisville Profi t Potential
Kansas City
NDVR Kansas City Profi t Potential
NDVR Portland Profi t Potential
The remaining sections of this chapter demonstrate how to search for the four
workbooks in Table 6–10 on drive E, how to create a workspace from the four workbooks,
and fi nally how to link the three region workbooks to consolidate the data into the NDVR
Company Profi t Potential workbook.
Search JabSto ::

Custom Search