Microsoft Office Tutorials and References
In Depth Information
Using cell references in formulas
Creating references to other worksheets in the same workbook
You can refer to cells in other worksheets within the same workbook just as easily as you
refer to cells in the same worksheet. For example, to enter a reference to cell A9 in Sheet2
into cell B10 in Sheet1, do this:
Select cell B10 in Sheet1, and type an equal sign.
Click the Sheet2 tab.
Click cell A9, and then press Enter.
After you press Enter, Sheet1 once again becomes the active sheet. Select cell B10, and you
can see that it contains the formula =Sheet2!A9.
The worksheet portion of the reference is separated from the cell portion by an
exclamation point. Note also that the cell reference is relative, which is the default when you select
cells to create references to other worksheets.
Creating references to worksheets in other workbooks
You can refer to cells in worksheets in separate workbooks in the same way you refer to
cells in other worksheets within the same workbook. These references are called external
references . For example, to enter a reference to Book2 in Book1, follow these steps:
Create a new workbook—Book2—by clicking the File tab, clicking New, and
doubleclicking Blank Workbook.
2. Click the View tab, click Arrange All, select the Vertical option, and click OK.
Select cell A1 in Sheet1 of Book1, and type an equal sign.
Click anywhere in the Book2 window to make the workbook active.
Click the Sheet2 tab at the bottom of the Book2 window.
Click cell A2. Before pressing Enter to lock in the formula, your screen should look
similar to Figure 12-1. Note that when you click to enter references to cells in external
workbooks, the inserted references are absolute.
Press Enter to lock in the reference.