Page 180 - Excel Progamming Weekend Crash Course

k540629 ch12.qxd 9/2/03 9:34 AM Page 155

Session 12 — Programming with Custom Formulas 155

Referencing Cells in Other Worksheets and Workbooks

A formula can reference a cell in another worksheet (within the same workbook) using the
following notation:

sheetname!CellReference
For example, the following formula displays 10 times the value in cell A2 of the work-
sheet sheet1:

=10*sheet1!A2

You can reference a cell in another workbook that is currently open in Excel by using
this syntax:
[WorkbookFileName]Worksheetname!CellReference

For example, this formula displays one half the value in cell D5 on sheet1 in Sales.xls:
=0.5*[Sales.xls]sheet1!D5

If the name of the linked workbook contains one or more spaces, you must enclose it in
single quotes:

=0.5*’[Sales Data.xls]sheet1’!D5
If the linked workbook is not currently open, the reference must include the complete
path to the file and must also use single quotes:

=0.5*’c:\data\[Sales.xls]sheet1’!D5

In Excel, use the Edit ➪ Links command to view all links to sources outside
the current workbook.
Tip

Use Caution When Using Links Between Workbooks

There are some situations that can play havoc with links between workbooks,
and you must be aware of them. If the source workbook (the one being linked
to) and the target workbook (the one containing the link reference) are both
open and you use the File ➪ Save As command to make a backup copy, Excel
automatically updates the target links to reflect this new name. This may not
be what you want. If you rename the source workbook when the target work-
book is not open, the new name is not reflected in the link references.