CalcTableRef
References into a Database Range
LibreOffice allows to reference the cell rectangle of a database range and parts of it by using the name of the database range, its field names and some keywords, without using direct references (like B1:D23).
Such expression looks like myData[[#Totals];[Sales]], for example. It makes formulas easier to read and maintain for the user.
 |
Note:
|
"structured references" are only implemented for a vertical orientated database-range. They are available since version LO 5.1.
The ODF standard currently has no means to save "structured references". When saving as an .ods file, a "structured reference" is converted into a direct reference with values at the time of saving. Efforts to bring "structured references" to ODF are tracked in tdf#162088 and oasis#4162.
When saving as an .xlsx file, "structured references" are retained.
Setting up a table
Spreadsheet "tables" are defined by database ranges ( ▸ ). See (text/scalc/01/12010000.xhp). The following is mandatory for using "structured references":
- Tables must have a name.
- Tables must be vertically orientated.
- For use with .xlsx file, tables must have labels.
- The labels names must follow the named range rules (link to naming rules)
The examples in the following sections use this table
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Region | Sales | Seniority |
| 2 | Smith | West | 21 | 5 |
| 3 | Jones | East | 23 | 11 |
| 4 | Johnson | East | 9 | 7 |
| 5 | Taylor | West | 34 | 11 |
| 6 | Brown | East | 23 | 15 |
| 7 | Walker | East | 12 | 4 |
| 8 | Edwards | East | 15 | 12 |
| 9 | Thomas | West | 17 | 10 |
| 10 | Wilson | West | 31 | 3 |
| 11 | Total | 2 | 185 | 8.67 |
The cell range A1:D11 was defined as database range "myData". The options "Contains column labels" and "Contains totals row" were checked when defining the database range.
Reserved reference keywords
[#Headers]
The keyword [#Headers] references the row of field names (aka column labels).
Example: The expression myData[#Headers] references the cells A1:D1.
If the database range has no labels, a #REF! error is generated.
[#Data]
The keyword [#Data] references the data records of the database range, without labels and totals.
Example: The expression myData[#Data] references the cell rectangle A2:D10.
The short form myData[] can be used as well.
[#Totals]
The keyword [#Totals] references the row of totals.
Example: The expression myData[#Totals] references the cells A11:D11.
If the database range has no totals, a #REF! error is generated.
[#All]
The keyword [#All] references the entire database range including labels and totals.
Example: The expression myData[#All] references the cells A1:D11.
[#This Row]
This keyword describes an implicit intersection.
Example: If the expression myData[#This Row] is used in a formula in cell F2, it references A2:D2. If the same expression is used in a formula in cell F5, it references A5:D5.
The intersection is only possible with rows of the database range. A similar 'This Column' does not exist.
If a database row does not exist in the row where the formula is placed, a #VALUE! error is generated.
Hints for those switching from Excel to Calc:
Excel does not allow this keyword for the entire table range but only for the data record rows, and produces an error in case of headers or totals row.
Excel uses in its UI the short form myData[@]. This implicit intersection operator @ is not available in Calc.
Referencing data in tables
A "structured reference" to cells in a database range has the form:
name of database range […]
The part inside the square brackets can be a keyword (see above), a field name in square brackets or a combination of them.
In case a single keyword or a single field name is used, single brackets instead of the double brackets are allowed.
Example: myData[#Headers] instead of myData[[#Headers]]
or myData[Region] instead of myData[[Region]]
Field name in square brackets
To address the array of all values in the records that belong to the same field use the form [field name]. The referenced cell range does not include label and total.
Example: The expression myData[[Region]] or its simplified form myData[Region] references the cells B2:B10.
If the database range has no label row, the generic labels Column1, Column2, … can be used.
Excel allows to omit the name of the table, if the formula cell belongs to the table. In Excel the formula =SUM(myData[Sales]) in cell C11 could be written as =SUM([Sales]). That is not possible in Calc.
Combinations
To reference a combination of the labels row and data records use [#Headers];[#Data] or [#Headers],[#Data]. The separator is the same separator as for function parameters. So whether to use a semicolon or a comma depends on the setting in ▸ ▸ ▸ . Texts in the help use always a semicolon.
Example: myData[[#Headers];[#Data]] references the cells A1:D10.
To reference a combination of data records and totals row use [#Data];[#Totals]
Example: myData[[#Data];[#Totals]] references the cells A2:D11.
A combination [#Headers];[#Totals] is not possible as that would result in two separate cell rectangles.
To address several adjacent columns use the range operator :.
Example: The formula myData[[Name]:[Sales]] addresses the cells A2:C10.
Using not adjacent columns is not possible since it would reference two separate cell rectangles.
The reference via field name and the use of a reference keyword can be combined, first the keyword, then function separator and last the field name in brackets.
Example: myData[[#Totals];[Sales]] references the cell C11.
Using "structured references"
Warning: "structured references" acts always so as if the database range has a vertical orientation. Using a "structured reference" on a horizontal orientated database range gives no error message, however the results are not valid.
A direct use as e.g. =myData[#Headers] is possible. The generated array Names Region Sales Seniority spills over 4 columns.
Do not use the formula =myData. That will copy the entire database range in LibreOffice, but it will produce an error when Excel opens the document. Use the formula =myData[#All] instead.
The output array or value of a "structured reference" can be used as input to other functions.
Example: The cells in row 11 have these formulas
B11 =COUNTA(UNIQUE(myData[Region]))
C11 =SUM(myData[Sales])
D11 =AVERAGE(myData[Seniority])
If a formula uses a "structured reference" and it references an array of more than one cell, then the formula has to be handled as array formula. You will see the formula with enclosing curly brackets in the input line then, e.g.{=myData[#Headers]}.ToDo: add link to help page for array formula