Page 102 - Excel Workbook for Dummies

10_798452 ch05.qxp 3/13/06 7:44 PM Page 85
Chapter 5: Building Formulas 85
When you refer to a cell that resides in another worksheet of the same workbook, you
preface the cell address with the sheet name separated by an exclamation point (!),
following this syntax:

Sheet1!A1

When you refer to a cell that resides in a sheet in another workbook file, you need to
include the filename (enclosed in square brackets) as well as the sheet name and cell
reference, following this syntax:

‘[file.xls]Sheet1’!A1

Many times, you use external references to create a link to values in another sheet
that need to be brought forward to the new worksheet. When you set up a link rather
than just pasting in a static value, any changes made to the value in the original cell is
automatically updated in the linked cell.

The easiest way to create an external reference link is by copying the cell to the
Clipboard (Ctrl+C) and then pasting it with the Paste Special command (Edit➪Paste
Special) using its Paste Link button.

Try It

Exercise 5-7: Building Formulas with External Links

Use your Practice Formulas.xls workbook to get experience with constructing links
using external references:

1. Position the cell cursor in cell C9 of the Formulas worksheet that contains the
SUM formula totaling the values in the cell ranges A2:A8, B4:B8, and C4:C8 and
then copy the value in this to the Clipboard (Ctrl+C).

2. Switch to the Ext Ref worksheet in the workbook and then position the cell
cursor in cell B3 of this sheet.
3. Choose Edit➪Paste Special and then click its Paste Link button, and then use
AutoFit to widen column B.

The value 157889.20 appears in cell B3 of the Ext Ref sheet and the following link
formula with the external reference appears on the Formula bar:
=Formulas!$C$9
4. Enter the heading Formulas Sheet Total in cell A3 and, if necessary, widen
column A of the Ext Ref sheet sufficiently to display the entire heading.
5. Switch back to the Formulas worksheet and then change the value in the source
cell A2 of this sheet from 34 to 56.
Note the effect that increasing this source value has on the total in cell C9 of this
worksheet.
6. Switch to Ext Ref worksheet, position the cell cursor in cell B3, and verify that
cell B3 in this worksheet now contains the same value as C9 in the Formulas
worksheet, thanks to the linking formula you created there.
7. Open a new blank workbook and then enter the heading Practice Formulas
Workbook Total in cell A2, widen column A to display the entire heading, and
then increase the display magnification percentage to 200%.
8. Save the workbook file with the filename External Link.xls in your Chapter 5
folder in the My Practice Spreadsheets folder.