Microsoft Office Tutorials and References
In Depth Information
Changing the types of your references
In other words, precede the cell address with the worksheet name, followed by an
exclamation point. Here’s an example of a formula that uses a cell on the Sheet2 worksheet:
=A1*Sheet2!A1
This formula multiplies the value in cell A1 on the current worksheet by the value in cell A1
on Sheet2 .
If the worksheet name in the reference includes one or more spaces, you must enclose it in single quotation marks.
(Excel does that automatically if you use the point-and-click method when creating the formula.) For example, here’s
a formula that refers to a cell on a sheet named All Depts :
=A1*'All Depts'!A1
Referencing cells in other workbooks
To refer to a cell in a different workbook, use this format:
=[WorkbookName]SheetName!CellAddress
In this case, the workbook name (in square brackets), the worksheet name, and an
exclamation point precede the cell address. The following is an example of a formula that uses a
cell reference in the Sheet1 worksheet in a workbook named Budget :
=[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, here’s a formula that refers to
a cell on Sheet1 in a workbook named Budget For 2013 :
=A1*'[Budget For 2013.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 fi nd it. Here’s an example:
=A1*'C:\Users\ user name \My Documents\[Budget For 2013.xlsx]Sheet1'!A1
A linked fi le can also reside on another system that’s accessible on your corporate network.
The following formula refers to a cell in a workbook in the files folder of a computer
named DataServer :
15
='\\DataServer\files\[budget.xlsx]Sheet1'!$D$7
Search JabSto ::




Custom Search