Document Solutions for Excel, .NET Edition | Document Solutions
Features / Formulas / Set Table Formula
In This Topic
    Set Table Formula
    In This Topic

    Table formula refers to a formula that is used as a structured reference in the worksheet instead of using it as an explicit cell reference. Structured reference in a table formula is the combination of table and column names in a spreadsheet with syntax rules that must be applied while creating a table formula.

    For instance, let us consider an example of a table formula in a spreadsheet.

    Table Formula

    The structured reference components in the above table formula are described below.

    Components Description
    Table Name References the table data, without any header or total rows. You can use a default table name, such as Table1, or change it to use a custom name.

    Example: DeptSales is a custom table name in the table formula.

    For more information on how to add custom names, see Defined Names.

    Column Specifier Column specifiers use the names of the columns they represent. They reference column data without any column header or total row. Column specifiers must be enclosed in [] square brackets when they are written in the table formula.

    Example: [SalesAmount] and [ComAmt]

    Item Specifier Refers to a specific portions of the table such as total row.

    Example: [#Totals] and [#Data]

    Table Specifier Represents the outer portions of the structured reference. Outer references follow table names and are enclosed within the square brackets.

    Example: [[#Totals],[SalesAmount]],[[#Data],[ComAmt]]

    Structures Reference Represented by a string that begins with the table name and ends with the column specifier.

    Example: DeptSales[[#Totals],[SalesAmount]] and DeptSales[[#Data],[ComAmt]]

    Reference operators

    In DsExcel .NET, reference operators are used to combine column specifiers in a table formula.

    Shared below is a table that describes the reference operators along with structured reference components and cell range corresponding to the table formula.

    Operators Description Example
    :(colon) range operator All of the cells in two or more adjacent columns. =DeptSales[[SalesPerson]:[Region]]
    ,(comma) union operator A combination of two or more columns. =DeptSales[SalesAmount],DeptSales[ComAmt]
    (space) intersection operator The intersection of two or more columns. =DeptSales[[SalesPerson]:[SalesAmount]]DeptSales[[Region]:[ComPct]]

    Special item specifier

    Special item specifier refers to a particular area in a table formula which is identified either with a # prefix or with an @ prefix.

    DsExcel .NET supports the following types of special item specifiers:

    Special Item Specifier Description
    #All To the entire table including column headers, data and totals (if any).
    #Data Only the data rows
    #Headers Only the header rows
    #Totals Only the total row. If there is none, it returns null.
    #This Row Cells in the same row as the formula
    @ Cells in the same row as the formula

    Refer to the following example code to set table formula in your spreadsheets.

    C#
    Copy Code 
    // Define Data
worksheet.Range["A1:E3"].Value = new object[,]
    {
        {"SalesPerson", "Region",  "SalesAmount", "ComPct", "ComAmt"},
        {"Joe", "North", 260, 0.10, null},
        {"Robert", "South", 660, 0.15, null},
    };

worksheet.Tables.Add(worksheet.Range["A1:E3"], true);
worksheet.Tables[0].Name = "DeptSales";
worksheet.Tables[0].Columns["ComPct"].DataBodyRange.NumberFormat = "0%";

//Use table formula in table range.
worksheet.Tables[0].Columns["ComAmt"].DataBodyRange.Formula = "=[@ComPct]*[@SalesAmount]";

//Use table formula out of table range.
worksheet.Range["F2"].Formula = "=SUM(DeptSales[@SalesAmount])";
worksheet.Range["G2"].Formula = "=SUM(DeptSales[[#Data],[SalesAmount]])";
worksheet.Range["H2"].Formula = "=SUM(DeptSales[SalesAmount])";
worksheet.Range["I2"].Formula = "=SUM(DeptSales[@ComPct], DeptSales[@ComAmt])";