Referencing tables in formulas

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

See also

• Formula calculation
• Tables