Understanding Structured Reference Syntax Rules

Structured references have additional syntax rules listed as follows:

• Matching brackets are required for tables, specifiers, and special characters.

• Characters with special meaning require an escape character.

• Spaces can be used in certain areas to make the structured reference easier to read.

All table, column, and special item specifiers must be enclosed in matching 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]]

All column headers are text strings, but do not require quotes when they are used in a structured reference. If a column header contains numbers or dates, such as 2004 or 1/1/2004, these are still considered text strings. Because column headers are text strings, you cannot use expressions within brackets, for example:

=DeptSalesFYSummary[[2004]:[2002]]

If a table column header contains one of the following special characters, the entire column header must be enclosed in brackets. This means double brackets are required in a column specifier with the following special characters: space, tab, line feed, carriage return, comma (,), colon (:), period (.), left bracket ([) , right bracket (]), pound sign (#), single quotation mark ('), double quotation mark ("), left brace ({), right brace (}), dollar sign ($), caret (^), ampersand (&), asterisk (*), plus sign (+), equal sign (=), minus sign (-), greater than symbol (>), less than symbol (<), and division sign (/).

The following structured reference includes a column specifier that contains special characters:

=DeptSalesFYSummary[[Total$Amount]]

The only exception to this is if the only special character that is used is a space character, for example:

=DeptSales[Total Amount]

The following characters have special meaning and require the use of a single quotation mark (') as an escape character: left bracket ([), right bracket (]), pound sign(#), and single quotation mark (').

The following example illustrates a structured reference that contains a character with a special meaning:

=DeptSalesFYSummary['#OfItems]

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

=DeptSales[ [SalesPerson]:[Region] ]

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

See Also

Using Operators and Special Items

Using Structured References