2.1.860 Part 1 Section 18.17.2.3, Cell References

a.   The standard does not specify the productions for cell-reference using EBNF.

Office writes the productions for cell-reference using the following EBNF:

 cell-reference= external-cell-reference | A1-reference ;
 external-cell-reference= sheets-prefix, A1-reference ;
 book-prefix= workbook-index, "!" ; 
 sheets-prefix = single-sheet-prefix |
   sheet-range, "!" ; 
 single-sheet-prefix = single-sheet, "!" ;
 single-sheet= [workbook-index] sheet-name |
   apostrophe,  [workbook-index] sheet-name-special,  apostrophe ;
 sheet-range= [workbook-index], sheet-name, ":", sheet-name |
 apostrophe,  [workbook-index] sheet-name-special, ":", sheet-name-special,  apostrophe ; 
 workbook-index =
   "[",  whole-number-part,  "]" ;
 sheet-name=
   sheet-name-characters ;
 sheet-name-characters=
   sheet-name-character,  {sheet-name-character} ;
 sheet-name-character=
   character – (operator  |  apostrophe  |  "["  |  "]"  |  "\"  |  "?") ;
              (* any character except operator or ', [, ], \, or ? *)
 apostrophe= "'"   ;
 space = " "   ;
 sheet-name-special=
   sheet-name-start-character-special,  
     [ [sheet-name-characters-special], sheet-name-end-character-special] ;
 sheet-name-start-character-special=
   character  –  (apostrophe  |  "*"  |  "["  |  "]"  |  "\"  |  ":"  |  "/"
 |  "?" | ? Unicode character END OF TEXT ? )  ;
 (* any character, including operator, except ', *, [, ], \, :, /, ?, or END OF TEXT *)
 sheet-name-end-character-special=
   sheet-name-start-character-special  ;
 sheet-name-characters-special=
   sheet-name-character-special,  {sheet-name-character-special}  ;
 sheet-name-character-special=
   apostrophe, apostrophe  |
   character  –  (apostrophe  |  "*"  |  "["  |  "]"  |  "\"  |  ":"  |  "/"
   |  "?" | ? Unicode character END OF TEXT ? )  ;
 (* any character, including operator, except ', *, [, ], \, :, /, ?, or END OF TEXT *)

b.   The standard does not specify how external book index can be used to access a cell in external workbook.

In Office, a nonzero index represents a reference to an external book in an external link collection.

c.   The standard refers to absolute cell reference behavior when the position of the cell containing the formula changes.

Excel does not relate the behavior of cells during move operations to relative or absolute references.

d.   The standard states that functions which take 3D references are AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, SUM, VAR, VARA, VARP, and VARPA.

In Office, the following functions also take 3D references: AND, AVEDEV, DEVSQ, GEOMEAN, HARMEAN, KURT, MEDIAN, NPV, OR, SKEW, SUMSQ, TRIMMEAN, and ZTEST.

e.   The standard states 3-D references shall not be used in multi-cell formulas.

In Office, 3-D references can be used in array or multi-cell formulas within the same limits as in a single-cell formula.

Show: