tableDesktop version

Structured references

Product: MyOffice Professional, MyOffice Standard, MyOffice Education

Application: Spreadsheet Desktop version

You can use structured references with the so-called "smart" tables, which are Microsoft Excel spreadsheets with the table formatting applied.

By default, smart tables are named Table1, Table2, etc., and consist of the following elements:

•Heading line

•Data area

•Result line

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.

Below are examples of regular and structured references:

Description	Regular reference	Structured reference
Reference to cell range of the Sales column in Table1	=SUM(B2:B8)	=SUM(Table1[Sales])
Reference to the data area in Table2	=SUM(A2:C8)	=SUM(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 the table, you do not need to specify the name of the table in this reference. If the table name is specified in the reference for any reason, it is automatically deleted when you enter the formula. For example, the formula =SUM(Table1[Sales]) will be automatically replaced with =SUM([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, under 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 heading line and the result line (if any)
#Headers	The heading line only
#Data	Data lines only
#Totals	Result line 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 the structured references are stated below.

Entire table

Data only

Headings only

Results only

Table Table1

Table1[#All]

Table1

Table1[#Data]

Table1[#Headers]

Table1[#Totals]

Column N

Table1[[#All],[N]]

Table1[N]

Table1[[#Data],[N]]

Table1[[#Headers],[N]]

Table1[[#Totals],[N]]

Columns from N to M

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

Table1[[N]:[M]]

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

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

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

Enter a structured reference into a formula

Formulas with structured references are entered as usual.

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

1.Start typing the table name. A drop-down list will show the names of all smart 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:

•Click the table name with the left mouse button.

•Select the table name using the ↓ and ↑ keys and press 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 name with the mouse.

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

The selected element will be added to the formula.

If the reference to the table data area (e.g., [#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 heading line).

Work with smart tables

"Smart" tables are displayed in MyOffice Spreadsheet the same way they were saved in the Excel. For example, if the result line was disabled in Excel, MyOffice Spreadsheet displays the table without the result line. Enabling or disabling table areas and filtering using the headings is not supported in MyOffice Spreadsheet.

If necessary, you can create the result line manually. To do this, enter the formula in any cell of the row following the last row in the table.

When working with a "smart" table, you can perform the following operations:

•Edit column names. Column names are automatically updated in the structured references of the table.

•Insert rows and columns between the existing rows and columns (but not at the end of the table).

•Copy "smart" tables within one document or from document to document, provided that both documents are open in MyOffice Spreadsheet. The copied table is automatically assigned a unique name. In all structured references, the name of the original table is automatically replaced with the name of the copied table.

Calculated columns

If you want to add a column where all cells contain the same formula to a table, create a calculated column. You can use any column in the table that does not contain data to create a calculated column.

To make a column calculated, type the desired formula in any cell of that column and press Enter.

All cells in the column will automatically be filled with the formula you entered.

The following rules apply to the calculated columns:

•If you edit a formula in any cell of a calculated column, the same formula editing is automatically performed in the remaining cells of that column.

•If the data in a column is partially deleted or regular data is entered instead of a formula, the column ceases to be calculated (formula changes cease to apply to the whole column).

•If the column consistency is restored manually (all cells contain the same formula again), the column becomes calculated again.

Automatic expansion of smart tables

Smart tables are auto-expanded when you enter data in adjacent cells located in the column to the right of the smart table. If there is no result line in the table, auto-expansion is also performed when you enter data in the adjacent cells located in the row following the last table row.

Data can be entered:

•Manually

If a formula is entered in an adjacent column, that column becomes calculated.

•From the clipboard. The automatic expansion is performed when data is inserted to the left/bottom of the table or when data is inserted simultaneously in both the table and adjacent cells.

•By means of automatic filling of the cells where the reference cell is one or multiple cells of the smart table.

The automatic expansion of smart tables has the following limitations:

•The automatic expansion area should not contain any data.

•The data inserted does not overlap with another "smart" table.

The current version of the application does not support the automatic expansion of "smart" tables when autofilling cells if a table cell with a structured reference is selected as a reference cell.

Was this helpful?

Yes