I defined tables as Tables (Insert>Table)

In defined Tables, you can use specific Table Formula syntax, where you can refer to Table using Table name, to Table column header using column header in specific way, to Table column using column header like [ColumnHeader], to cell in specific column in same row like [@ColumnHeader], etc. When you any cell in defined Table, an additional menu selection is added, where you can change Table properties, like name, size/range, etc.

When you enter some text adjacent to righmost colum header in Table, the additional column is added automatically. When you insert a column into Table, the new column is implemented automatically, and a column is named automatically. You also can change Table size/range from menu selection.

When you enter anything into some cell immediately below last row of Table, or press Tab when rightmost lower cell is selected, a row is added automatically into table. To add several rows at go, you have to change Table size/range.

When the whole column of Table contains same formula or format, then whenever a new row is added, or formula is edited in any sell of column, this formula/format is applied to added row(s) automatically. Editing the formula in column in a way which doesn't update the formula in whole column, results in stopping of automatic formula update for this column.

Whenever you edit Table name or column header in Table, all references to it are updated automatically everywhere in your workbook. Dragging whole columns into new positions in Table doesn't affect your formulas, except cases where you refer to column position numbers in your formula. (e.g. VLOOKUP(). But you can use there something like MATCH(HeaderName,TableName[#[header]],0) instead of column number - I haven't Excel available at moment and I don't remember exact syntax.)

Thank you for all of your help with this.

So, I named the range of data and then I named the table. I then copied your formula and changed formula to reflect my table and range name, but it's not working for me. I get the dreaded message "There's a problem with this formula..." I have attached a screen shot of the message.

What am I doing wrong? I have no doubt it's user error.

Attached Images

ExcelError.png (23.4 KB, 17 views)