Referencing other sheets or workbooks

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

When a formula refers to other cells, the references need not be on the same sheet as the formula. To refer to a cell in a different worksheet, precede the cell reference with the sheet name followed by an exclamation point. Here's an example of a formula that uses a cell reference in a different worksheet (Sheet2):

=Sheet2!A1+1

You can also create link formulas that refer to a cell in a different workbook. To do so, precede the cell reference with the workbook name (in square brackets), the worksheet name, and an exclamation point. Here's an example:

=[Budget.xlsx]Sheet1!A1

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:

Referencing Data in a Table

Excel 2007 supports a special type of range that has been designated as a table (using the Insert Tables Table command). Tables add a few new twists to formulas.

When you enter a formula into a cell in a table, Excel automatically copies the formula to all of the other cells in the column - but only if the column was empty. This is known as a calculated column. If you add a new row to the table, the calculated column formula is entered automatically for the new row. Most of the time, this is exactly what you want. If you don't like the idea of Excel entering formulas for you, use the SmartTag to turn this feature off. The SmartTag appears after Excel enters the calculated column formula.

Excel 2007 also supports "structured referencing" for referring to cells within a table. The table in the accompanying figure is named Table1.

A

0

c

□ E

J

i

2 |

Munlli

n CjHfor nki

D Aria ui id

D Wellington Q TlHjI

■ •1

3

Jan

«

34

16

1Z5

4

ret

J5

39

22

13«

S

Mir

UH

44

11

177

£

Tnui:

W

it!

74

J IS,

7 -■ *

r H

S>«u ■ ^

mw

You can create formulas that refer to cells within the table by using the column headers. In some cases, this may make your formulas easier to understand. But the real advantage is that your formulas will continue to be valid if rows are added or removed from the table. For example, these are all valid formulas:

=SUM(Table1[Washington])

=Table1[[#Totals],[California]]

=Table1[[#Headers],[California]]

=SUM(Tablel[[#This Row],[California]:[Washington]])

The last formula, which uses [#This Row], is valid only if it's in a cell in one of the rows occupied by the table.

If the linked workbook is closed, you must add the complete path to the workbook reference. Here's an example:

= 'C:\Budgeting\Excel Files\[Budget For 2008.xlsx]Sheet1' !A1

Although you can enter link formulas directly, you can also create the reference by using normal pointing methods. To do so, the source file must be open. When you do so, Excel creates absolute cell references. If you plan to copy the formula to other cells, make the references relative.

Working with links can be tricky. For example, if you choose the Office Save As command to make a backup copy of the source workbook, you automatically change the link formulas to refer to the new file (not usually what you want to do). Another way to mess up your links is to rename the source workbook when the dependent workbook is not open.

4 PREV

Was this article helpful?

0 0

Post a comment