You can use operators and special items in the structured reference. The structured reference can be unqualified or fully qualified.
For added flexibility in specifying ranges of cells, you can use the following reference operators to combine column specifiers. The Cell Range column is a general example.
| Structured Reference | Refers To | Operator | Cell Range |
|---|---|---|---|
| =DeptSales[[SalesPerson]:[Region]] | All of the cells in two or more adjacent columns | : (colon) range operator | A2:B7 |
| =DeptSales[SaleAmt],DeptSales[ComAmt] | A combination of two or more columns | , (comma) union operator | C2:C7, E2:E7 |
| =DeptSales[[SalesPerson]:[SaleAmt]] DeptSales[[Region]:[ComPct]] | The intersection of two or more columns | (space) intersection operator | B2:C7 |
For added convenience, you can also use special items to refer to various portions of a table, such as the Totals row, to make it easier to refer to these portions in formulas. The following are the special item specifiers that you can use in a structured reference:
| Special Item Specifier | Refers To | Cell Range |
|---|---|---|
| =DeptSales[#All] | The entire table, including column headers, data, and totals (if any) | A1:E8 |
| =DeptSales[#Data] | Just the data | A2:E7 |
| =DeptSales[#Headers] | Just the header row | A1:E1 |
| =DeptSales[#Totals] | Just the total row. If none exists, then it returns null | A8:E8 |
| =DeptSales[#This Row] | Just the portion of the columns in the current row. #ThisRow cannot be combined with any other special item specifiers. Use it to force implicit intersection behavior for the reference or to override implicit intersection behavior and refer to single values from a column. |
When you create a calculated column, you often use a structured reference to create the formula. This structured reference can be unqualified or fully qualified. For example, to create the calculated column called, ComAmt, that calculates the amount of commission in dollars, you can use the following formulas:
| Structured Reference | Example | Comment |
|---|---|---|
| Unqualified | =[SaleAmt]*[ComPct] | Multiplies the corresponding values from the current row |
| Fully qualified | =DeptSales[SaleAmt]*DeptSales[ComPct] | Multiples the corresponding values for each row for both columns |
If you are using structured references within a table, such as when you create a calculated column, you can use an unqualified structured reference, but if you use the structured reference outside of the table, you need to use a fully qualified structured reference.