My current project uses 6 tables. 4 tables contain source data and 2 are used to display data combined from the source tables. The four source tables have the same construction. The 2 "combine" tables have the same construction plus addtional columns containing formulae used to order the data. These formulae are entered in the table manually at design time (by adding 1 row and entering the formulae then deleting the row. The main procedure ic Sub Combine in Module 1. It clears all data from the "combine" tables; copies and pastes one of the source tables; compares values in the combined table to values a second source table and decides on a new combined value; then copies and pastes any rows in the 2nd source table which were not involved in the comparison. This last step disrupts the formulae in the sort columns which translate from summing cells in one row of the table to summing the same columns but now includes a block of rows below the insertion point. NOTE if the row is added at the end of the table the formulae above the insertion point are not changed.
I have repeated this action manually Home:Insert:Insert Table Rows Above and get the same formula disruption.
I have tried to alter the syntax of the SUM formulae I'm using [typically SUM(M9:T9) where 9 is the sheet row] so that I use table rows instead of sheet rows but can't find any way of doing that. The nearest I can get is the sample in cell A1 of sheet Eclectic which reproduces the type of result I get in my combine table.
My work around has been to build the tables then overwrite the formulae cells with the correct data. That is currently in a YesNo If in Sub Combine the for test purposes. This works well but is not neat. Any ideas.

Attached Files

Combined Eclectic rev 6 plus ranges.xlsm (362.5 KB, 12 views)