Excel 2010 Bible

589

CHAPTER

Linking and

Consolidating

Worksheets

IN THIS CHAPTER

Various methods of linking

workbooks

Consolidating multiple

worksheets

I

n this chapter, I discuss two procedures that you might find helpful:
linking and consolidation. Linking is the process of using references to
cells in external workbooks to get data into your worksheet. Consolidation
combines or summarizes information from two or more worksheets (which
can be in multiple workbooks).

Linking Workbooks

As you may know, Excel allows you to create formulas that contain refer-

ences to other workbook files. In such a case, the workbooks are linked in

such a way that one depends upon the other. The workbook that contains

the external reference formulas is the dependent workbook (because it con-

tains formulas that depend upon another workbook). The workbook that

contains the information used in the external reference formula is the source

workbook (because it’s the source of the information).

When you consider linking workbooks, you may ask yourself the following

question: If Workbook A needs to access data in another workbook

(Workbook B), why not just enter the data into Workbook A in the first

place? In some cases, you can. But the real value of linking becomes appar-

ent when the source workbook is being continually updated by another per-

son or group. Creating a link in Workbook A to Workbook B means that in

Workbook A, you always have access to the most recent information in

Workbook B because Workbook A is updated whenever Workbook B

changes.

Linking workbooks also can be helpful if you need to consolidate different

files. For example, each regional sales manager may store data in a separate