How to reference table column header name and table row number?

1
votes

I'm trying to populate a new table in a new worksheet with data from an existing table in a different worksheet. I need to reference the column header name because the positions of the columns may change.

I currently have this:

=TableName[@[ColumnHeaderName]]

This works but the problem is when I try to sort any of the columns in the new table, it doesn't sort because it is referencing the same row in the existing table. I'm guessing I need to reference the column name and row number, but when I try "=TableName[@[ColumnHeaderName]] 2:2" it displays #VALUE!.

Any help would be greatly appreciated.

1
excelexcel-formula

1 Answers

2
votes

It seems that you are working with excel tables (i.e. ListObjects).
The formula:

=TableName[@[ColumnHeaderName]]

refers to the
- Table: TableName
- Column: ColumnHeaderName
- Row: Row of the cell where the formula is entered from the Worksheet where the Table is located.

Therefore if the TableName header is located at row 6 of Sheet1 and the formula is entered in row 8 of Sheet2 it will return the value in column ColumnHeaderName, row 8 of Sheet1 which is the row 2 of the Table.Body (e.i. DataBodyRange)

To return the first value in column ColumnHeaderName of the TableName use this formula:

=INDEX(TableName[ColumnHeaderName],1)

TableName[@[ColumnHeaderName]] refers to the same row of the table and
TableName[ColumnHeaderName] refers to the entire column.
Also, any of the above formulas exclude the header of the Table.
To refer to the header use:

=TableName[[#Headers],[ColumnHeaderName]]

if you want to refer the entire header use:

=TableName[#Headers]

Since you are entering the formula in another excel table, lets named it Table2, in order to have the row number dynamically updated enter this formula:

=INDEX(TableName[ColumnHeaderName],ROW()-ROW(Table2[#Headers]))