Structured References

Spread for WPF supports structured reference formulas in tables. This topic explains how structured references in spreadsheets help refer to table columns, rows, or specific data. It focuses on how to access data in structured formats within a table without using cell addresses.

The components of a structured reference are illustrated in the following image. This formula adds total sales and tax amounts.

Let's understand how to use structured references in tables in detail in the following sections.

Using Operators and Special Fields

You can use operators and special fields in the structured reference to manipulate data.

Use the following reference operators to combine column specifiers to specify cell ranges more flexibly. The "Cell Range" column shows examples in the table below.

Special fields allow you to refer to different parts of a table, such as a Total row, making it easier to refer to these parts in formulas. The following are the special field specifiers that can be used for structured references.

When you create a calculated column, you often use a structured reference to create the formula. A structured reference can be unqualified or fully qualified.

An unqualified structured reference can be used within a table, for example, when creating a column that calculates a fee in dollars. However, when you use a structured reference outside a table, make sure it's a fully qualified structured reference.

Understanding Structured Reference Syntax Rules

This section outlines the rules for writing structured references, such as how to reference table names, column headers, and ensure proper formats when using brackets and quotes.

• Use matching brackets for specifiers

  All table, column, or special item specifiers must be enclosed inside brackets. A specifier that contains other specifiers requires outer matching brackets to enclose the inner matching brackets of the other specifiers. For example,

  =DeptSales[[SalesPerson]:[Region]]

• Do not use quotes for column headers

  All column headers are text strings, but there’s no need to put quotes around them in a structured reference. Even if a column header contains numbers or dates (like 2004 or 1/1/2004), they are still considered text strings. Also, you cannot use expressions within the brackets for the column headers. For example,

  =DeptSalesFYSummary[[2004]:[2002]]

• Use matching brackets for column headers with special characters

  If a column header has special characters, enclose the whole header in matching brackets. These special characters include space, tab, line feed, carriage return, comma (,), colon (:), period (.), left bracket ([), right bracket (]), pound sign (#), single quotation mark ('), double quotation("), left brace ({), right brace (}), dollar sign ($), caret (^), ampersand (&), asterisk (*), plus sign (+), equal sign (=), minus sign (-), greater than symbol (>), less than symbol (<), and division sign (/). For example,

  =DeptSalesFYSummary[[Total$Amount]]

  !type=note

  Note: The only exception to this is if the only special character that is used is a space character. For example, =DeptSales[Total Amount]

• Use single quotation marks for special characters

  If a column header contains a character having a special meaning, such as bracket ([), right bracket (]), pound sign(#), and single quotation mark ('), you should use a single quotation mark (') as an escape character for it. The following example illustrates a structured reference that contains a character with a special meaning. For example,

  =DeptSalesFYSummary['#OfItems]

• Use space characters to improve the readability

  You can add space characters to improve the readability of a structured reference. You can use one space after the first left bracket ([) and before the last right bracket (]). You can also add a space after a comma, as shown in the following example. For example,

  =DeptSales[ [SalesPerson]:[Region] ]

  =DeptSales[[#Headers], [#Data], [ComPct]]

Using Structured References

You can add structured references in a table using the Formula property of the IRange interface. A cell outside the table can have a formula with a table reference; make sure that the table name is unique and valid.

The following example code shows how to use a structured reference in a formula. Here, it sums the "Value" column in the table.

C#

// Use structured references.
spreadSheet1.Workbook.Worksheets[0].Cells[1, 1].Text = "Name" ;
spreadSheet1.Workbook.Worksheets[0].Cells[1, 2].Text = "value" ;
spreadSheet1.Workbook.Worksheets[0].Cells[2, 1].Text = "Aoki" ;
spreadSheet1.Workbook.Worksheets[0].Cells[2, 2].Value = 50;
spreadSheet1.Workbook.Worksheets[0].Cells[3, 1].Text = "Gill" ;
spreadSheet1.Workbook.Worksheets[0].Cells[3, 2].Value = 10;
spreadSheet1.Workbook.Worksheets[0].Cells[4, 1].Text = "Smith" ;
spreadSheet1.Workbook.Worksheets[0].Cells[4, 2].Value = 78;
spreadSheet1.Workbook.Worksheets[0].Tables.Add(1, 1, 4, 2, YesNoGuess.Yes, null , "Table" );
spreadSheet1.Workbook.Worksheets[0].Cells[5, 2].Formula = "SUM(Table[value])" ;
spreadSheet1.Workbook.Worksheets[0].Columns[1, 2].ColumnWidth = 150;

VB

' Use structured references.
spreadSheet1.Workbook.Worksheets(0).Cells(1, 1).Text = "Name"
spreadSheet1.Workbook.Worksheets(0).Cells(1, 2).Text = "value"
spreadSheet1.Workbook.Worksheets(0).Cells(2, 1).Text = "Aoki"
spreadSheet1.Workbook.Worksheets(0).Cells(2, 2).Value = 50
spreadSheet1.Workbook.Worksheets(0).Cells(3, 1).Text = "Gill"
spreadSheet1.Workbook.Worksheets(0).Cells(3, 2).Value = 10
spreadSheet1.Workbook.Worksheets(0).Cells(4, 1).Text = "Smith"
spreadSheet1.Workbook.Worksheets(0).Cells(4, 2).Value = 78
spreadSheet1.Workbook.Worksheets(0).Tables.Add(1, 1, 4, 2, YesNoGuess.Yes, Nothing, "Table")
spreadSheet1.Workbook.Worksheets(0).Cells(5, 2).Formula = "SUM(Table[value])"
spreadSheet1.Workbook.Worksheets(0).Columns(1, 2).ColumnWidth = 150