%PRODUCTNAME Calc lets you reference data in Database tables by using a special notation, a “database table reference”, for cell references inside the table. This special notation aims to improve the readability of formulas that reference cells inside a database table.
Database tables
Spreadsheet "tables" are defined by database ranges (). In addition to the name of the database, the following is mandatory for using database table references:Tables must be vertically oriented.The column label names must follow the named range rules.Tables must have column labels, if interoperability with Microsoft Excel is required.
Example
The table below contains values used in examples later on in this document.
The cell range A1:D11 was defined as the database range "myData ". The options and were checked when defining the database range.
Referencing data in tables
A database table reference has the form name of database_range[…]. The part inside the square brackets can be a reserved reference keyword, a field name in square brackets, or a combination of the two.In cases where a single keyword or a single field name is used, use single brackets instead of double brackets.
Example
myData[#Headers] instead of myData[[#Headers]] or myData[Region] instead of myData[[Region]].
Reserved reference keywords
KeywordUsageExample[#Headers]The keyword [#Headers] references the row of field names (column labels). It is the first row of the database range. If the database range has no labels row defined (Contains columns row), a #REF! error is generated.The expression myData[#Headers] references the cells A1:D1.[#Data]The keyword [#Data] references the data records of the database range, excluding the column label row and the totals row.The short form myData[] can be used as well.The expression myData[#Data] references the cell rectangle A2:D10.[#Totals]The keyword [#Totals] references the row of totals. It is the last row of the database range.If the database range has no line of totals defined (), a #REF! error is generated.The expression myData[#Totals] references the cells A11:D11.[#All]The keyword [#All] references the entire database range including column labels and totals.The expression myData[#All] references the cells A1:D11.[#This Row]This keyword describes an implicit intersection.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.
Field name in square brackets
To reference 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 totals.
Example
The expression myData[[Region]] or its simplified form myData[Region] references the cells B2:B10. If the database range has no label row, generic labels like Column1, Column2 can be used.In Microsoft Excel, if the formula cell belongs to the table, then the name of the table may be omitted. For example, the formula =SUM(myData[Sales]) in cell C11 could be written as =SUM([Sales]). Omission of the table name is not yet possible in Calc.
Combinations
Columns and data records
To reference a combination of the column labels and data records, use the format [#Headers];[#Data] or [#Headers],[#Data], where the separator is the same separator as for function parameters that is defined in .
Data records and total row
To reference a combination of data records and totals row, use [#Data];[#Totals]. For example, myData[[#Data];[#Totals]] references the cells A2:D11.A combination like [#Headers];[#Totals] is not possible as that would result in two disjoint cell rectangles.
Adjacent columns
To reference several adjacent columns, use the range operator “:”. For example, the formula myData[[Name]:[Sales]] addresses the cells A2:C10.
Non-adjacent columns
The use of non-adjacent columns is not possible since it would reference two separate cell rectangles.
Field name and keyword
The reference via field name and the use of a reference keyword can be combined. First state the keyword, then the function separator, and last the field name in brackets. For example, myData[[#Totals];[Sales]] references the cell C11.Implicit intersection in formulas