Microsoft Office Tutorials and References

In Depth Information

**Cell and Range References**

continued

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 (relative, 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 21.

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 reference 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