3.2.3.1.1 Structure References

  • Article

A structure reference has the following form: 

 structure-reference= 
 [table-identifier], intra-table-reference ;
 table-identifier=
 [book-prefix], table-name ;
 table-name=
 name ;
 intra-table-reference=
 spaced-lbracket, inner-reference, spaced-rbracket |
 keyword |
 '[', [simple-column-name], ']' ;
 inner-reference=
 keyword-list |
 [keyword-list, spaced-comma], column-range ;
 keyword=
 "[#All]" | "[#Data]" | "[#Headers]" | "[#Totals]" | "[#This Row]" ;
 keyword-list=
 keyword |
 "[#Headers]", spaced-comma, "[#Data]" |
 "[#Data]", spaced-comma, "[#Totals]" ;
 column-range=
 column, [":", column] ;
 column=
 simple-column-name |
 spaced-lbracket, {space}, simple-column-name, {space}, spaced-rbracket ;
 simple-column-name=
 [any-nospace-column-character, {any-column-character }], any-nospace-column-character ;
 escape-column-character=
 "'" | "[" | "]" | "#" ;
 any-column-character=
 character - escape-column-character | 
 "'", escape-column-character ;
 any-nospace-column-character=
 any-column-character – space ;
 spaced-comma=
 [space], ",", [space] ;
 spaced-lbracket=
 "[", [space] ;
 spaced-rbracket=
 [space], "]" ;

Structure references define rectangular sections of tables using special syntax, table column names, and reserved keywords instead of relative or absolute references. Structure references do not need adjustment if tables they refer to have been modified. They also provide a mechanism to define areas using meaningful table column names instead of less helpful cell references.

table-name is the name of the table the structure reference refers to. If it is missing, then the formula containing the structure reference must be entered into a cell that belongs to a table, and that table’s name is used as the table-name. table-name must be a name of a table. It must not be any other user-defined name.

table-name[] refers to all cells in table-name except Header Row and Total Row.

table-name[#Data] refers to all table-name’s cells except Header Row and Total Row. It is equivalent to the form table-name[].

table-name[#Headers] refers to all cells in table-name’s Header Row.

table-name[#Total Row] refers to all cells in the table-name’s Total Row

table-name[#All] refers to the entire table area. table-name[#All] is the union of table-name[#Headers], table-name[#Data], and table-name[#Total Row]

table-name[column-name] refers to all cells in the column named column-name except the cells from Header Row and Total Row.

table-name[[column-name]] refers to all cells in the column named column-name except the cells from Header Row and Total Row.

table-name[[column-name1]:[column-name2]] refers to all cells from column named column-name1 through column named column-name2 except the cells from Header Row and Total Row.

table-name[[keyword],[column-name]], where keyword is one of #Headers, #Total Row, #Data, #All, refers to the intersection of the area defined by table-name[keyword] and all cells from the column named column-name.

table-name[[#Data],[column-name]] is equivalent to table-name[column-name]

table-name[[keyword],[column-name1]:[column-name2]], where keyword is one of #Headers, #Total Row, #Data, #All, refers to the intersection of the area defined by table-name[keyword] and all cells from the table from column named column-name1 through column named coumn-name2.

table-name[[#Headers],[#Data],[column-name]] is the union of table-name[[#Headers],[column-name]] and table-name[[#Data],[column-name]]

table-name[[#Data],[#Total Row],[column-name]] is the union of table-name[[#Data],[column-name]] and table-name[[#Total Row],[column-name]]

table-name[[#Headers],[#Data],[column-name1]:[column-name2]] is the union of table-name[[#Headers], [column-name1]:[column-name2]] and table-name[[#Data], [column-name1]:[column-name2]]

table-name[[#Data],[#Total Row], [column-name1]:[column-name2]] is the union of table-name[[#Data], [column-name1]:[column-name2]] and table-name[[#Total Row], [column-name1]:[column-name2]]

table-name[[#This Row],[column-name]] refers to the cell in the intersection of table-name[column-name] and the current row; for example, the row of the cell that contains the formula with the structure reference. table-name[[#This Row],[column-name1]:[column-name2]]refers to the cells in the intersection of table-name[[column-name]:[column-name2]] and the current row; for example, the row of the cell that contains the formula with such structure reference. These two forms allow formulas to perform implicit intersection using structure references.

#This Row must not be combined with any of #Headers, #Total Row, #Data, #All.