Hi guys!
Really ran out of ideas, so looking for some bright new ones. A relatively straightforward problem, however trying to give some background and hoping it not to become too cumbersome. I have an Excel 2016 xlsm worksheet with a table that has header and one empty row which would autoexpand when new data is added. There are a couple of columns to enter or (mainly) paste new data. Also some columns that have formulas which also replicate with the autoexpanding table + one column with fomulas located outside the table range. The entire table is also mapped for an XML export. Not overly complicated, but several columns with formulas and the XML export which I have tested to work, so I'm very reluctant to try building it up from scratch, so looking for ways to find the bug with the current setup.
I created a dummy scenario from scratch on a screenshot below. So, the table would be between B1:C2 by default and would expand when pasting data into cell B2. No warnings or error message with sandbox scenario. In the live environment, I have a formula outside the table range (like on screenshot, i.e. column A) that is pre-filled + some formulas inside the table that replicate along (not present on the example).
While copy-pasting data into cell B2 in this sandbox test it works fine. However trying this on a "live" table will give me the Excel warning message as also shown below. As far as I can see, the table itself will actually present correct values after OK-ing this popup, however what will be messed up will be the pre-filled formulas in column A.
The first formula in A2 will stay OK, however formula A3 would change from instead =B3&C3 into =B6&C6 (and continue onwards just fine).
The only way I have been able to copy-paste data (anything above 1 row) into the table with NO warning message and the formulas being consistent in column A was that I would not paste it into the first cell, i.e. B2, but rather keep the first row empty and start from cell B3 - but I wouldn't prefer that.
While I do understand it can be challenging to just spew out accurate help based on this, maybe someone has thoughts what would cause that kind of error message in Excel table autoexpand in general, as by default in a new autoexpanding table, pasting data like this will work fine.
Really ran out of ideas, so looking for some bright new ones. A relatively straightforward problem, however trying to give some background and hoping it not to become too cumbersome. I have an Excel 2016 xlsm worksheet with a table that has header and one empty row which would autoexpand when new data is added. There are a couple of columns to enter or (mainly) paste new data. Also some columns that have formulas which also replicate with the autoexpanding table + one column with fomulas located outside the table range. The entire table is also mapped for an XML export. Not overly complicated, but several columns with formulas and the XML export which I have tested to work, so I'm very reluctant to try building it up from scratch, so looking for ways to find the bug with the current setup.
I created a dummy scenario from scratch on a screenshot below. So, the table would be between B1:C2 by default and would expand when pasting data into cell B2. No warnings or error message with sandbox scenario. In the live environment, I have a formula outside the table range (like on screenshot, i.e. column A) that is pre-filled + some formulas inside the table that replicate along (not present on the example).
While copy-pasting data into cell B2 in this sandbox test it works fine. However trying this on a "live" table will give me the Excel warning message as also shown below. As far as I can see, the table itself will actually present correct values after OK-ing this popup, however what will be messed up will be the pre-filled formulas in column A.
The first formula in A2 will stay OK, however formula A3 would change from instead =B3&C3 into =B6&C6 (and continue onwards just fine).
The only way I have been able to copy-paste data (anything above 1 row) into the table with NO warning message and the formulas being consistent in column A was that I would not paste it into the first cell, i.e. B2, but rather keep the first row empty and start from cell B3 - but I wouldn't prefer that.
While I do understand it can be challenging to just spew out accurate help based on this, maybe someone has thoughts what would cause that kind of error message in Excel table autoexpand in general, as by default in a new autoexpanding table, pasting data like this will work fine.
Last edited:
