When you create a workbook containing several worksheets, you'll often want to reference the data in one worksheet when you build a formula in another worksheet. Setting up a connection between worksheets is called creating a link in Excel terminology. For example, if your workbook contains a separate worksheet for each sales region in the country, you could create a Summary worksheet that includes sales data from each of the supporting worksheets. Linked worksheets also provide an additional advantage: When you change the source worksheet, Excel updates the related information in the linked worksheet.
To learn how to link worksheets together in different workbooks, see "Linking Information Between Workbooks."
The following procedure shows you how to create formulas that link worksheets together. The workbook used as an example contains five worksheet tabs, named Summary, Northwest, South, Midwest, and West. The four regional worksheets contain quarterly sales data for each of a company's sales representatives active in the region. (The sales reps are listed individually by name.) The Summary worksheet presents an overview of the sales activity throughout the year and uses several SUM formulas to calculate the quarterly totals from each of the linked worksheets. You might want to use this worksheet structure in your own workbooks.
To create formulas that calculate totals from linked worksheets and place them on a single summary worksheet, follow these steps. Our example uses regional sales data.
The following screen shows the sample worksheet named Northeast, containing quarterly sales figures for the six sales reps active in the Northeast sales region.
ON THE WEB
The SalesSum.xls example is on the Running Office 2000 Reader's Corner page. For information about connecting to this Web site, read the Introduction.