Microsoft Office Tutorials and References
In Depth Information
The numbers in brackets refer to the relative position of the references. For example, R[–5]C[–3] specifies the cell
that appears five rows above and three columns to the left. Conversely, R[5]C[3] references the cell that appears
five rows below and three columns to the right. If you omit the brackets (or the numbers), it specifies the same
row or column. For example, R[5]C refers to the cell five rows below in the same column.
Although you probably won't use R1C1 notation as your standard system, it does have at least one good use.
R1C1 notation makes it very easy to spot an erroneous formula. When you copy a formula, every copied formula
is exactly the same in R1C1 notation. This remains true regardless of the types of cell references you use (relat-
ive, absolute, or mixed). Therefore, you can switch to R1C1 notation and check your copied formulas. If one looks
different from its surrounding formulas, it's probably incorrect.
However, you can take advantage of the background formula auditing feature, which can flag potentially incorrect
formulas. I discuss this feature in Chapter 22.
Referencing other sheets or workbooks
A formula can use references to cells and ranges that are in a different worksheet. To refer to a cell in a different
worksheet, precede the cell reference with the sheet name followed by an exclamation point. Note this 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 referen-
ce with the workbook name (in square brackets), the worksheet name, and an exclamation point (!), like this:
=[Budget.xlsx]Sheet1!A1+1
If the workbook name or sheet name in the reference includes one or more spaces, you must enclose it (and the
sheet name) in single quotation marks. For example:
='[Budget Analysis.xlsx]Sheet1'!A1+A1
If the linked workbook is closed, you must add the complete path to the workbook reference. For example:
='C:\MSOffice\Excel\[Budget Analysis.xlsx]Sheet1'!A1+A1
A linked file can also reside on another system that's accessible on your corporate network. The following for-
mula refers to a cell in a workbook in the files directory of a computer named DataServer.
='\\DataServer\files\[Budget Analysis.xlsx]Sheet1'!A1
If the linked workbook is stored on the Internet, the formula will also include the URL. For example:
='https://d.docs.live.net/86a61fd208/files/[Annual
Budget.xlsx]Sheet1'!A1
Although you can enter link formulas directly, you can also create the reference by using the normal pointing
methods discussed earlier. To do so, make sure that the source file is open. Normally, you can create a formula
by pointing to results in relative cell references. But when you create a reference to another workbook by point-
Search JabSto ::




Custom Search