Microsoft Office Tutorials and References
In Depth Information
Cell and Range References
Referencing other sheets or workbooks
When a formula refers to other cells, the references don’t need to 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):
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:
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:
=’[Budget For 2010.xlsx]Sheet1’!A1
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 2010.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 File➜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.