Microsoft Office Tutorials and References
In Depth Information
Referencing Cells in Other Sheets in a Workbook
With the three region sheets complete, the next step is to modify Sheet1, which
will serve as the consolidation worksheet containing totals of the data on the Louisville,
Kansas City, and Portland sheets. Because this sheet contains totals of the data, you need
to understand how to reference cells in other sheets in a workbook before modifying
Sheet1.
To reference cells in other sheets in a workbook, you use the sheet name, which
serves as the sheet reference , and the cell reference. For example, you refer to cell B5 on
the Louisville sheet as shown below.
=Louisville!B5
Using this method, you can sum cell B5 on the three region sheets by selecting cell
B5 on the Sheet1 sheet and then entering:
= Louisville!B5 + Kansas City!B5 + Portland!B5
A much quicker way to total the three cells is to use the SUM function as follows:
=SUM(Louisville:Portland!B5)
The SUM argument (Louisville:Portland!B5) instructs Excel to sum cell B5 on each
of the three sheets (Louisville, Kansas City, and Portland). The colon (:) between the ﬁ rst
sheet name and the last sheet name instructs Excel to include these sheets and all sheets
in between, just as it does with a range of cells on a sheet. A range that spans two or more
sheets in a workbook such as Louisville:Portland!B5 is called a 3-D range . The reference
to this range is a 3-D reference .
A sheet reference such as Portland! always is absolute. Thus, the sheet reference
remains constant when you copy formulas.
Circular References
A circular reference is a
formula that depends
on its own value. The
most common type is a
formula that contains a
reference to the same
cell in which the formula
resides.
You can enter a sheet reference in a cell by typing the sheet reference or by clicking
the appropriate sheet tab while in Point mode. When you click the sheet tab, Excel activates
the sheet and automatically adds the sheet name and an exclamation point after the insertion
point in the formula bar. Next, select or drag through the cells you want to reference on
the sheet.
If the range of cells to be referenced is located on several worksheets (as when
selecting a 3-D range), click the ﬁ rst sheet tab and then select the cell or drag through the
range of cells. Next, while holding down the SHIFT key, click the sheet tab of the last sheet
you want to reference. Excel will include the cell(s) on the ﬁ rst sheet, the last sheet, and
any sheets in between.
To Modify the Company Sheet
This section modiﬁ es the Company sheet by changing the sheet name, tab color,
and subtitle and then entering the SUM function in each cell in the range B5:B11. The
SUM functions will determine the total units on hand at the three regions, by company.
Cell B5 on the Company sheet, for instance, will contain the sum of the JVC DVR units
on hand in cells Louisville!B5, Kansas City!B5, and Portland!B5. Before determining the
totals, the following steps change the sheet name from Sheet1 to Company, color the tab,
and change the worksheet subtitle to Company Proﬁ t Potential.
3-D References
If you are summing
numbers on noncontigu-
ous sheets, hold down
the CTRL key rather than
the SHIFT key when
selecting the sheets.

Search JabSto ::

Custom Search