Microsoft Office Tutorials and References
In Depth Information
Using structured references
● The third item, [Qtr 1], is a column specifier. In our example, this corresponds to the
range B4:B7. However, because it follows the @ specifier, only cells in the range that
happen to be in the same row as the formula are included, or cell B4 in the example.
The second reference follows the second comma in the string and is essentially the
same as the first, specifying the other end of the range, or cell C4 in the example.
Understanding structured reference syntax
Here are some of the general rules governing the creation of structured references:
● Table-naming rules are the same as those for defined names. See “Naming cells and
cell ranges” earlier in this chapter.
● You must enclose all specifiers in matching brackets.
To make structured references easier to read, you can add a single space character in
any or all of the following locations:
❍ After the first opening (left) bracket (but not in subsequent opening brackets)
❍ Before the last closing (right) bracket (but not in subsequent closing brackets)
After a comma
Column headers are always treated as text strings in structured references, even if the
column header is a number.
You cannot use formulas in brackets.
● You need to use double brackets in column header specifiers that contain one of the
following special characters: tab, line feed, carriage return, comma, colon, period,
opening bracket, closing 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—for
example, Sales[[$Canadian]]. Space characters are permitted.
Using operators with column specifiers
You can use three reference operators with column specifiers in structured references—a
colon (:), which is the range operator; a comma (,), which is the union operator; and a space
character ( ), which is the intersection operator.
For example, the following formula calculates the average combined sales for quarters 1
and 4 using a comma (the union operator) between the two structured references:
=AVERAGE(Regional13[Qtr 1],Regional13[Qtr 4])