Excel warning message on table autoexpand

Question

I have an Excel xlsm worksheet with a defined table that has a header and 1 empty row which would autoexpand when data is added. Please see screenshot for a dummy example.

When adding rows manually, it will autoexpand and work fine. I will start to have problems when pasting data into the table which is the objective.

In case pasting anything from 2 rows or more into cell B2, I will get the warning message as presented on screenshot. The only way no warning is shown is if I add data one by one manually OR paste data into cell B3, i.e. leave the first row empty, which I'm reluctant to do.

Just to clarify - things work fine for a new dummy table, but I'd really need to fix my existing table which otherwise works great for XML-export.

Also - expansion of the "live" table range works as expected. The suspicious part is that I'm presented this warning message which is not a standard procedure. However the tangible problem at hand is the set formulas in column A that are outside the table range, set and are not supposed to autoexpand (but are referring to cells within the table).

The formula in the cell A2 would stay the same, however formula in A3 would turn from =B3&C3 into something like =B6&C6

While it might be difficult to pinpoint the exact issue - maybe someone has ideas what could cause this warning message to be shown on table autoexpansion in the first place ?

Answer 1

The problem is actually caused by the fact that, in column A you have references to the cells below the Table that you then paste data into. The warning dialog is simply alerting you to the fact that the formula in column A have been changed.

The solution is to replace the normal references in the formulas in column A with structured references.

The appropriate formula for all the cells in column A would thus be:

=IFERROR(YourTableName[Quantity]&YourTableName[Item],"")

---

Notes:

Don't forget to replace YourTableName with your actual table name.

The simple way to get the structured references for cells in a Table is to click on the Table cells when typing in the formula.