Spread Windows Forms 13.0 Product Documentation
Spread Windows Forms 13.0 Product Documentation / Developer's Guide / Customizing Sheet Interaction / Customizing Interaction with a Sheet / Creating Tables / Understanding Structured References / Using Operators and Special Items
In This Topic
    Using Operators and Special Items
    In This Topic

    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.