Table in state where new rows do not get the formula or formatting from the rest of the rows.

Hi again Tom-Con,

Did you try the VBA code solution that I posted? I have made a modifications to the code since initial posting. The code still basically does the same thing. However, it now copies the formula from the first row of the table in each column containing formulas and saves the formula to a variable. It then clears the column before re-populating the column with the formula. I am not sure if it makes any difference doing this but anything is worth a try.

I have also included provision for un-protecting to allow modification and re-protecting after modification. See the constant at the top where you will need to edit and insert your password in lieu of "ossie". If no passwords in your project then this part of the code is ignored.

If using the code and you allow it to loop through all worksheet and all Tables then it is very much quicker than attempting to edit the problem areas individually. If any worksheets do not have Tables then the code ignores them because it loops through the tables that are present but does not error it no tables found on a sheet. When using the code, wait for the message box indication that it is finished.

I totally agree with Andreas that the use of inconsistent formulas in a column does cause problems at times.

Re your comment as follows. *"*Microsoft either needs to fix these bugs or [preferably] provide us with a UI to "set column format" and "set column formula", say.  The "Tables" feature is not all that new and should not be this buggy." I suggest that from within Excel you select File -> Feedback and Send a Frown and follow the prompts to describe the problem. This is the correct place to report perceived bugs and please include your email address so that Microsoft can contact you if they require more information.

It is not "consistently wrong" so i cannot reliably demonstrate that it happens with a demonstration test case.  But it happens (to me) "reasonably often".

I suggest to show us a file when it happens again, then we can discuss further.

If it really happens often, I expect you upload a sample file in a few days!?

Andreas.

Sorry, thanks for the feedback; I appreciate it. But this idea unfortunately does not work. Its one that would seem obvious, like it should work. But indeed i have tried filling the formula down, and when that same formula is in every row of a column, it is not filling down into new rows, in these cases, unfortunately.  It is not "consistently wrong" so i cannot reliably demonstrate that it happens with a demonstration test case.  But it happens (to me) "reasonably often".

And, the same issue occurs with formatting.  I can select the entire column with the little down arrow in the header, and format it, and then that formatting will still not be used in new rows added to that column, in instances in which the "table gets into this erroneous state".

Note also that i checked my options and indeed those boxes are checked.

Thanks for trying to help out here.

The reason for this behavior is the use of inconsistent formulas, the formula in a table column is usually the same in all cells.

If you break that rule, Excel stops filling.

Check also this options:

If that doesn't solve the issue in your table, re-enter the formula in the top cell of the column and fill the formula down the column till the end of the table.

Andreas.