Cell References
Each set of horizontal cells in a worksheet is a row, and each set of vertical cells is a column. A cell's row and column combination designates the location of that cell. [GAn implementation is encouraged to not unnecessarily limit the number of rows and columns in a worksheet. ]
A cell reference designates one or more cells on the same worksheet. Using references, one can:
Use data contained in different parts of the same worksheet in a single formula.
Use the value from a single cell in several formulas.
Refer to cells on other sheets in the same workbook, and even to other workbooks. (References to cells in other workbooks are called links.)
A cell reference has the following form:
cell-reference:name[ [ [ [ [ workbook-name ] ] [ sheet-name : ] ] sheet-name ! ] A1-reference[ [ [ [ [ workbook-name ] ] [ sheet-name : ] ] sheet-name ! ] A1-reference : A1-reference[ [ [ [ [ workbook-name ] ] [ sheet-name : ] ] sheet-name ! ] R1C1-reference[ [ [ [ [ workbook-name ] ] [ sheet-name : ] ] sheet-name ! ] R1C1-reference : R1C1-reference
workbook-name:book-name-start-character [ book-name-characters ]
book-name-start-character: any character except ', *, [, ], :, and ?
book-name-characters:book-name-characters book-name-character
book-name-character:any character except *, [, ], :, and ?
sheet-name:sheet-name-start-character [ sheet-name-characters ]
sheet-name-start-character: any character except ', *, [, ], \, :, /, and ?
sheet-name-characters:sheet-name-characters sheet-name-character
sheet-name-character:any character except *, [, ], \, :, /, and ?
A relative cell reference is based on the relative position of the cell that contains the formula and the cell to which the reference refers. If the position of the cell that contains the formula changes, the reference is changed along with it.
An absolute cell reference always refers to the absolute location of a cell. If the position of the cell that contains the formula changes, the absolute reference remains the same.
A mixed cell reference has either an absolute column and relative row, or an absolute row and relative column.
It is possible to process the same cell or set of cells on multiple worksheets within a workbook, using a 3-D reference. A reference of this type is made up of the cell reference, preceded by a range of worksheet names, and an exclamation mark character (!
), in that order. A 3-D reference can be used to refer to cells on other sheets, to defined names, and to create formulas by using the following functions: AVERAGE
, AVERAGEA
, COUNT
, COUNTA
, MAX
, MAXA
, MIN
, MINA
, PRODUCT
, STDEV
, STDEVA
, STDEVP
, STDEVPA
, SUM
, VAR
, VARA
, VARP
, and VARPA
.
3-D references shall not be used in multi-cell formulas.
!
), in that order. This allows cells in one worksheet to be referenced in another worksheet of the same workbook. MonthlyTotals!D1:D12
might be used from within a sibling (or the same) worksheet of MonthlyTotals
to refer to those 12 cells. A5
and B6:C10
each designate one area, and D3:D5,E12:F15
designates two areas (the comma (,
) being the union operator). AREAS
(§There are two cell reference styles: A1 (§3.17.2.3.1) and R1C1 (§3.17.2.3.2).