Referencing tables in formulas (original) (raw)
EPPlus / OOXML has a somewhat different way of referencing cells inside tables, than used in the Excel GUI.
Addresses within tables can be referenced either relative or absolute in a formula.
A table address always starts with the table name, followed by a section of the table and/or a column wrapped in brackets. For example:
| Syntax | Description |
|---|---|
| Table1[#all] | Reference the whole table including headers and totals |
| Table1[] | Reference the data part of the table excluding the headers and totals. This is the same as Table1[#Data] |
| Table1[Column1] | Reference the data part of the column "Column1" within the table Table1 |
| Table1[[#This Row],[Column2]] | Reference the cell at the same row as the formula cell of column "Column2" within the table Table1. In Excel this would look like Table1[@Column2]. This syntax is often used in the ExcelTableColumn.CalculatedColumnFormula |
| Table1[[#Data],[#Totals],[Column2]]]] | Reference the data and totals part of the column "Column2" within the table Table1 |
| Table1[[#Headers],[Column1]] | Reference the header cell of the column "Column1" within the table Table1 |
| Table1[[Column1]:[Column2]] | Reference the data part of column "Column1" to "Column2" within the table Table1 |
| Table1[[#Data],[#Totals],[Column1]:[Column2]] | Reference the data and totals part of column "Column1" to "Column2" within the table Table1 |
Using the table column's property, CalculatedColumnFormula is useful with this syntax:
tbl.Columns[9].CalculatedColumnFormula = string.Format("SUM(MyDataTable[[#This Row],[{0}]])",tbl.Columns[8].Name); //Reference the prior column within the current row tbl.Columns[9].CalculatedColumnFormula = string.Format("MyDataTable[[#Headers],[{0}]]",tbl.Columns[9].Name); //Reference to a column header tbl.Columns[9].CalculatedColumnFormula = string.Format("MyDataTable[[#Totals],[{0}]]",tbl.Columns[9].Name); //Reference to a column total