Excel® 2007 Bible

Chapter 27. Linking and Consolidating Worksheets

<feature><title>In This Chapter</title>

• Linking workbooks

• Consolidating worksheets

</feature>

In this chapter, I discuss two procedures that are common in the world of spreadsheets: 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

When you link worksheets, you connect them together in such a way that one depends on the other. The workbook that contains the link formulas (also known as external reference formulas) is called the dependent workbook. The workbook that contains the information used in the external reference formula is called the source workbook.

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 apparent when the source workbook is being continually updated by another person 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 ...