Keep formulas referencing original column name from table after adding column to table in excel

Question

In excel have a table named "MyTable" with two columns:

NAME	DATE
Mark	2020.01.01
Jane	2021.02.15

I have a formula to find the max value of the "DATE" column "=MAX(MyTable[Date])" which returns "2021.02.15"

I need to update "MyTable" with new data, but it has additional columns and the columns aren't in the same order:

NAME	Dollars	DATE
Mark	500	2020.01.01
Jane	250	2021.02.15

If I paste the new data with the additional column into "MyTable" my max date formula automatically updates to "=MAX(MyTable[Dollars])" which now returns "500" not "2021.02.15"

Is there a way to replace the data in the table with data that contains additional columns and columns in a different order without having every formula in the workbook shift it's column reference to an incorrect column? (in the actual problem I'm trying to solve, the source data is from a government website so I don't control the source formatting, which changes frequently. And there are 275+ columns so it's not feasible to manually rearrange the table every time there is a change)

I would use match() to find each column heading and index() from there. — Solar Mike
Would this work? =MAX(MyTable[[Date]:[Date]]) referencing the from:to ranges makes it behave as locked when dragging your formula to a different column. I'm unable to reproduce your error though. — P.b

ASchenck ASchenck · Accepted Answer · 2021-11-04T19:34:32

I found something that works:

=MAX(INDIRECT("MyTable[Date]"))

The use of INDIRECT lets me hard-code the reference as a text value so it won’t dynamically update if the column order in the table is changed.

Keep formulas referencing original column name from table after adding column to table in excel

1 Answers