Excel table auto-fills incorrect formula when inserting rows-CodePudding

I have an Excel 2019 Workbook in which I have a sheet with a range of cells defined as a table. Table columns are A to H, where columns A and C contains formulas and the others have to be manually compiled with information by users. The sheet is protected, with the cells in columns without formulas unlocked, so that users cannot alter formulas, but they are allowed to add rows (in the middle of the table, not at its end) as this can be required. The problem is that when adding rows, the formula auto-filled in column A for the row below the new one is incorrect.

Detailed explanation

Column A is used to assign an unique number to what si written in column B, so that if an element in column B is repeated, the corresponding number in column A is repeated as well
The first cell of the table in column A (A4) contains the value "1"
Cell A5 contains the formula "=IF(B5=B4;A4;A4 1)"
Cell A6 contains the formula "=IF(B6=B5;A5;A5 1)", and so on
If I add a row between 5 and 6, the formula in the new cell A6 is correct, but the formula in cell A7 becomes "=IF(B7=B5;A5;A5 1)", instead of the correct "=IF(B7=B6;A6;A6 1)"
Formulas in all the cells below A7 are correct
If I manually fill the formula in A7 form another cell by using the fill handle, the formula corrects itself

I know there is a similar thread MS Excel Auto-Fills Incorrect Formula When Adding Rows and I already tried some workarounds described there such as clear the column and then add the formula back again, but without results. My situation seems to be different because the formula is not present in the entire column because of the "1" in A4.

Even though the problem can be manually addressed with little effort via the fill handle, this is not a suitable solution since users may not correct the formula, which is needed for some other calculations in another sheet of the Workbook.

CodePudding user response：

The behaviour you describe is normal and nothing to do with tables. If you insert a row in between a cell and another cell it references directly in a formula, that formula will not adjust to refer to the new cell. In other words if A6 contains =A5 and you insert a row between rows 5 and 6, that =A5 will not suddenly alter to be =A6. If you need that behaviour, you would need to use something like INDEX or INDIRECT - for example:

=INDEX(A:A,ROW()-1)

will always refer to the cell in the row above the formula cell.