Page 234 - Excel 2007 Bible

16_044039 ch11.qxp 11/21/06 11:04 AM Page 191
Introducing Formulas and Functions
If the workbook name in the reference includes one or more spaces, you must enclose it (and the sheet
name) in single quotation marks. For example, here’s a formula that refers to a cell on Sheet1 in a work-
book named Budget For 2008:
=A1*’[Budget For 2008.xlsx]Sheet1’!A1
When a formula refers to cells in a different workbook, the other workbook doesn’t have to be open. If the
workbook is closed, however, you must add the complete path to the reference so that Excel can find it.
Here’s an example:
=A1*’C:\My Documents\[Budget For 2008.xlsx]Sheet1’!A1
A linked file can also reside on another system that’s accessible on your corporate network. The formula
below, for example, refers to a cell in a workbook in the files directory of a computer named DataServer.
=’\\DataServer\files\[budget.xlsx]Sheet1’!$D$7
Refer to Chapter 27 for more information about linking workbooks.
CROSS-REF
CROSS-REF
To create formulas that refer to cells not in the current worksheet, point to the cells rather
TIP
TIP
than entering their references manually. Excel takes care of the details regarding the workbook
and worksheet references. The workbook you’re referencing in your formula must be open if you’re going to 11
use the pointing method.
NOTE If you point to a different worksheet or workbook when creating a formula, you’ll notice that
NOTE
Excel always inserts absolute cell references. Therefore, if you plan to copy the formula to
other cells, make sure that you change the cell references to relative before you copy.
Using Formulas In Tables
One of the most significant new features in Excel 2007 is its support for tables. In this section I describe
how formulas work with tables.
CROSS-REF See Chapter 6 for an introduction to the new table features.
CROSS-REF
Summarizing data in a table
Figure 11.10 shows a simple table with three columns. I entered the data, and then converted the range to a
table by choosing Insert ➪ Tables ➪ Table. Note that I didn’t define any names, but the table is named
Table1 by default.

191