Forum Discussion
Excel not auto-filling formulas in an Excel Table
I finally understand what you want to do and believe you can achieve it by using structured references and an OFFSET function to pick-up the number from the row below.
In E2 the formula becomes:
=[@[Amt Added]]+[@[Amt Withdrawn]]+[@Return]+OFFSET([@[Current Value]],1,0)and in F2:
=[@Return]+OFFSET([@[VOT Amt]],1,0)Make sure that these have filled down all the way to the bottom of the table.
When you now insert row 3, both formulas will auto-complete in the new row. See attached.
As to why the initial formula doesn't work, take a look at the formulas before you insert row 3.
From the start the formulas in E2 and F2 refer to cells E3 and F3. Insert row 3 and the references will update to E4 and F4 (i.e. two rows down), just as one would expect. The formulas on what is now row 4, still reference E5 and F5. I.e. only one row down. The same for all rows down the table.
Columns E and F now will no longer auto-complete as you have inconsistent formulas in them. Row 3 will be left blank and, the formulas in row 2 are wrong.
You are using 365, so my preference is to use Tables for the data entry, but dynamic arrays (based upon entire columns of the table) for the calculation.
If you choose to place the formula within the table, however, ensure you have no direct cell references as they represent 'a disaster waiting for somewhere to happen'. In the present case, that is when inserting a row gives an inconsistent formula that breaks the table.
If you use a defined name to hold the relative reference to the cell below
"Table formula"
=[@values]+below
"using A1 notation (from cell G9)"
below
= Sheet1!G10
"or using the R1C1 notation"
= Sheet1!R[1]Cthat will avoid inconsistent formulas.
