Strange formula behaviour in table

[Integral]

I have a table with a number of columns that contain formulas, and some that do not.
Some of the columns use an INDEX MATCH construction.
Recently, the table started behaving very oddly: When I add a new table row by writing just under the last row, in the new row one of the columns with INDEX MATCH copied the formula to the two cells to the left (which contained only data validation - one using a list referring to a named range, the other an INDIRECT function). Only one of the INDEX MATCH columns behaved strangely, the others behaved normally.

Apart from the fact that the formulas were unwanted in the cells, it created a circular reference which Excel of course did not like.

After spending a long time troubleshooting I finally turned the table into a range and back into a table which seems to have solved the problem.

I am, however, curious as to what may have caused this. Any suggestions?

[kev_]

My imediate reaction was that this was caused inadverently by the user
- Excel Tables are very clever
- BUT if the user changes a cell in the table, that affects future table behaviour
- that would give you the "symptoms" you descibe

However:
- I have manualy replicated what happened to your table
- it is easy to drag (last row)cells with formulas to unwanted cells and thereby cause the circular reference
- future table behaviour is changed
- new rows added with table exhibiting "new" behaiour
- but I always get a warning "There is aready data here. do you want to replace it"

Using VBA with Application.DisplayAlerts = False
- your problem could be replicated and the warning message did not appear

[Integral]

Thanks for the reply.
A few points of clarification:
The table is three rows + headers
The offending formula was in column I. It was in neither G or H (I checked all rows).
A similar formula was in columns E and F. These did not pose a problem.
I expanded the table by writing text in A5 just under the table, after which the formula in column I was replicated to I5 but also G5 and H5 (not higher rows in G and H)

There is no VBA or macros in the workbook.

Regards

[Richard Buttrey]

It would be easier to comment if you uploaded the workbook

[Integral]

TBM Budget test version.xlsx

Here is the workbook - see the "Actuals" sheet.

[Richard Buttrey]

Is it possible that a range of cells from one of the rows was copied and accidentally pasted to a cell immediately underneath the table that was offset one column to the right?

[Integral]

Well - if you delete row 5 and start a new row row 5 by typing some text in A5 I think you will see the error again.