Forum Discussion
Table Formulas Not Expanding
Morning all - need assistance with table formulas not expanding. I've spent a few hours troubleshooting and changing the various settings under Options one at a time, as well as changing cell formats in the table itself, but nothing has worked. Short version - this table helps my supervisors automate tracking of employee attendance. column A and B are ID and Name, columns C-F are formulas. When tabbing across the last row to the point the table successfully creates a new row below, only the formula in column F copies down and applies to the new row.
I've turned all these settings on and off individually with no luck - under File > Options >:
-Formulas > Formula AutoComplete
-Proofing > AutoCorrect > AutoFormat As You Type > Automatically as you work > Fill Formulas in tables to create calculated columns
-Advanced > Enable Autocomplete for cell values > Automatically Flash Fill
I've even copied the entire tab to a fresh workbook. In the new workbook, with the user list as the only sheet, the formulas copy perfectly in C-F when adding a new row to the table. I'm attaching the de-identified copy for review, but it doesn't show the same behavior as the main file - which of course i can not post since it houses employee information.
Does anyone have any tips for troubleshooting this issue?
Thanks
Joe
Try the following:
- Clear the cells in columns C to F in all data rows of the table except the first one.
- Select C2, press F2 then Enter. The formulas should be copied to all table cells in column C.
- Do the same for D2, E2 and F2.
- Now test creating a new table row.
- Hans - thanks as always for the help! Performed the steps as described, but the formulas still do not apply down the entire column. Even attempted two other items:
1. Clear all formulas, redefine the area as a range, redefine as a table and do the same - still no luck.
2. Create fresh table from blank sheet. Still same issue occurs.
Is it possible there is yet another setting not appropriately selected, or settings conflicting with one another? Seems to be at the level of the workbook, at not at the level of the sheet or table.I'm stumped - this behavior is controlled by the second setting you mentioned in the first post:
- Proofing > AutoCorrect > AutoFormat As You Type > Automatically as you work > Fill Formulas in tables to create calculated columns.
Perhaps the workbook has become corrupted...
