Working with 3-D Cell References

3-D References

The rows and columns in a worksheet represent two dimensions and the collection of worksheets a third. A 3-D cell reference is a reference that specifies not only the rows and columns of a cell range, but also the sheet or sheets. Therefore, a 3-D cell reference is ‘ Sheet Range ’! Cell Range . Sheet Range is the range of sheets and Cell Range is the cell range within those sheets. The sheet range can be either a single sheet or a range of adjacent sheets. If the sheet range includes more than one sheet, you specify the first and last sheet in the range separated by a colon. To reference range B2:B20 on Sheet 1 worksheet type ‘Sheet1’!B2:B20. To reference this same range on three adjacent sheets named Sheet1, Sheet2, and Sheet3 type ‘Sheet1:Sheet3’!B2:B20. The quotation marks are necessary if worksheet names have spaces.

If you change the positions of worksheets or remove a worksheet in a workbook such 3-D cell reference become inaccurate. You can rename worksheets and 3-D references are automatically updated to reflect the change. You can enter 3-D references either by typing the reference in the formula or by using your mouse to select the worksheet cells in the workbook. Do this by first selecting the sheet range followed by the cell range.

Practice

Let’s insert a 3-D reference into the Singers.xls workbook. The starting cash balance for each month after January is based on the ending cash balance of the previous month. For February, this amount needs to equal cell B6 in the January worksheet, so you’ll enter a reference to this cell now in the February worksheet.

Click the February tab and then click cell B5.
Type = but do not press the Enter key.

3d cell reference image

Click the January tab.
Click cell B6 and then click the Enter button (the green check mark on the Formula bar) in the January worksheet window.

3d cell reference image

The February sheet is redisplayed. As indicated in the formula bar for the February worksheet, Excel automatically inserted the formula =January!B6 in cell B5. The starting cash balance for February changes to 21, 870 and the ending cash balance changes to 19,180.

3d cell reference image

Repeat steps 1 through 4 for the remaining 10 months of the year to calculate each month’s starting cash balance based on the previous month’s ending cash balance. At the end of 2003, the ending cash balance for the entire year is 2,170. If the ending cash balance is different, check the formulas you entered into cell B5 for each of the monthly worksheets in the workbook. The value should always be equal to the value of cell B6 for the previous month. Save your work and go on to the next section on Consolidating Data from Several Worksheets.

Return to Course Topics.

3-D References		Search:

	E-mail Internet Access Software Assistance Computer Labs Software Licensing Web Publishing Calendaring Computer Security Unix Training

	Working with 3-D Cell References 3-D References The rows and columns in a worksheet represent two dimensions and the collection of worksheets a third. A 3-D cell reference is a reference that specifies not only the rows and columns of a cell range, but also the sheet or sheets. Therefore, a 3-D cell reference is ‘ Sheet Range ’! Cell Range . Sheet Range is the range of sheets and Cell Range is the cell range within those sheets. The sheet range can be either a single sheet or a range of adjacent sheets. If the sheet range includes more than one sheet, you specify the first and last sheet in the range separated by a colon. To reference range B2:B20 on Sheet 1 worksheet type ‘Sheet1’!B2:B20. To reference this same range on three adjacent sheets named Sheet1, Sheet2, and Sheet3 type ‘Sheet1:Sheet3’!B2:B20. The quotation marks are necessary if worksheet names have spaces. If you change the positions of worksheets or remove a worksheet in a workbook such 3-D cell reference become inaccurate. You can rename worksheets and 3-D references are automatically updated to reflect the change. You can enter 3-D references either by typing the reference in the formula or by using your mouse to select the worksheet cells in the workbook. Do this by first selecting the sheet range followed by the cell range. Practice Let’s insert a 3-D reference into the Singers.xls workbook. The starting cash balance for each month after January is based on the ending cash balance of the previous month. For February, this amount needs to equal cell B6 in the January worksheet, so you’ll enter a reference to this cell now in the February worksheet. Click the February tab and then click cell B5. Type = but do not press the Enter key. Click the January tab. Click cell B6 and then click the Enter button (the green check mark on the Formula bar) in the January worksheet window. The February sheet is redisplayed. As indicated in the formula bar for the February worksheet, Excel automatically inserted the formula =January!B6 in cell B5. The starting cash balance for February changes to 21, 870 and the ending cash balance changes to 19,180. Repeat steps 1 through 4 for the remaining 10 months of the year to calculate each month’s starting cash balance based on the previous month’s ending cash balance. At the end of 2003, the ending cash balance for the entire year is 2,170. If the ending cash balance is different, check the formulas you entered into cell B5 for each of the monthly worksheets in the workbook. The value should always be equal to the value of cell B6 for the previous month. Save your work and go on to the next section on Consolidating Data from Several Worksheets. Return to Course Topics.
Copyright © 1998-2004 The University of Alabama \| Disclaimer \| Contact: Help.Desk@ua.edu