Objective 13. Use a Summary Sheet | Chapter One. Creating a Worksheet and Charting Data

Table of contents:

Objective 14 Format Worksheets in a Workbook

A summary sheet is frequently used as a place where totals from other worksheets are displayed and charted.

Activity 1.23. Constructing Formulas that Refer to Cells in Another Worksheet

In this activity, you will use formulas in the Summary worksheet to display the total cost of gasoline from the other three worksheets. This technique updates the Summary worksheet whenever changes are made to the other worksheet totals.


1.	Make the Summary worksheet the active sheet. Locate the value in cell B3 and recall that the new total for Austin #1 is 163.08this value is no longer correct. As currently arranged, cells B3:B5 contain values that must be updated manually each time a change is made to the other worksheets that affect their values. A better method would be to construct formulas in these three cells that refer to the cells in the other worksheets, so that changes are automatic.
2.	Select the range B3:B5 and press to delete the contents of the cells, and notice that the columns in the chart no longer display. Because the chart is based on data from the cells whose contents you deleted, the chart can no longer display any data. You will replace the values in these three cells with formulas that update when changes are made that affect the totals on the other sheets.
3.	Click cell B3. Type = and then click the Austin #1 sheet tab. On the Austin #1 sheet, click cell E12, and then press to redisplay the Summary worksheet.
4.	Click B3 to select it again. Look at the Formula Bar and notice that instead of a value, the cell contains a formula that is equal to the value in a cell in another worksheet. Compare your screen with Figure 1.77. Figure 1.77. The Currency Style format with no decimal places that was applied to the cell is retained, and the chart is revised at the same time. By using a formula of this type, changes in cell E12 of the other worksheet will be automatically updated in this worksheet.

5.	Click cell B4. Type = and then click the Austin #2 sheet tab. Click cell E12 and press . Use the same technique to copy the total from the Dallas sheet to cell B5. Save your changes. The formulas in cells B3:B5 now display the totals from the other three worksheets. The format that was applied to the Summary worksheet cells before the contents were deleted is applied to the values from the formulas. If changes are made to the three detail worksheets that affect their totals, the new values will display on the Summary sheet and the chart will be updated accordingly. In this manner, the summary worksheet accurately displays the current totals from the other worksheets.