Structured references – Formulas and functions | How to use MyOffice Spreadsheet Desktop version macOS

Use of structured references

Formulas with structured references do not operate with cells or range references, but with the names of the table and columns of the table, as well as the names of table areas.

The examples of regular and structured references:

Description	Regular reference	Structured reference
Reference to cell range of the Sales column in Table1	=MAX(B2:B8)	=MAX(Table1[Sales])
Reference to the data area in Table2	=MAX(A2:C8)	=MAX(Table2[#Data])

references_2

Structured references can be used both in the table and outside of it.

If a formula with a structured reference is located directly in a table, it is not necessary to specify the table name in this reference. If a table name is specified in a reference for any reason, it is automatically removed when you enter a formula. For example, the formula =MAX(Table1[Sales]) will be automatically replaced with =MAX([Sales]).

If a formula with a structured reference is located outside the table, the name of the table must be specified.

To find out the name of a table, select any cell in it. The table name will appear on the Toolbar, in the Table section.

To copy the table name, select it by clicking it.

Structured reference syntax

Structured references can refer to the following data in a table:

Reference	Description
#All	The whole table, including the header row and the total row (if any)
#Headers	Header row only
#Data	Data rows only
#Total	Total row only
@Column name OR @ OR #This row	Cell at the intersection of the current row and the specified column (implicit intersection) #This row reference will automatically change to @

The examples of structured references can be found below:

Entire table

Data only

Headers only

Total only

Table Table1

Table1[#All]

Table1

Table1[#Data]

Table1[#Headers]

Table1[#Total]

Column N

Таблица1[[#All],[N]]

Table1[N]

Table1[[#Data],[N]]

Table1[[#Headers],[N]]

Table1[[#Total],[N]]

Columns N to M

Таблица1[[#All],[N]:[M]]

Table1[[N]:[M]]

Table1[[#Data],[N]:[M]]

Table1[[#Headers],[N]:[M]]

Table1[[#Total],[N]:[M]]

Enter a structured reference in a formula

Formulas with structured references are entered as usual.

To specify the name of a table in a formula, proceed with the following:

1.Start typing the table name. A drop-down list will show the names of all tables that are contained in the document.

2.Select the name of the desired table using the mouse or the keyboard in one of the following ways:

•Left-click the table name.

•Select the table name using the ↓ and ↑ keys and press enter Enter.

The table name will be entered into the formula.

To enter the column name or a reference to the table area (for example, #Totals or @), do the following:

1.Enter the [ (left square bracket) symbol. The drop-down list will display the column names and the references to the table areas.

2.Select the desired element from the list using the mouse or the keyboard:

•Left-click the element line.

•Select the element line with the ↓ and ↑ keys and press enter Enter.

The selected element will be added to the formula.

If the reference to the table data area (for example, [#Headers]) is entered correctly into a formula, it is highlighted in color, and the table highlights the range to which it refers (in this example, the header row).