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.
Here's an example of what I mean by the dashboard coupled with a database that just builds from the bottom. (And to assuage your concern, I have a database of over 9000 rows--all of my family financial transactions for a number of years--checks, credit cards, the whole bit--I always add to the bottom, and Excel remembers where I was last, so it usually opens to the bottom.)
But even if it doesn't, it's an easy matter to hit Command and the down arrow (on a Mac) from anywhere IN that database, in a column like date which is never empty, and Excel takes you to the bottom.
And then the "dashboard," where based on today's date and a self-selected N of your own choosing, it will display the most recent N days worth of rows.
Now, for some reason--maybe Riny_van_Eekelen knows a reason why this might happen--one of the two formulas auto-populates, but the other doesn't. I've tried several things to get it to start working, to no avail.
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.
wcstarks Glad it worked out, but obviously can't be sure why it didn't work at first without seeing your original file.
- Thank you. I tried putting your formulas into my original table and found that it still did not auto-fill either at the top or the bottom. I then opened the version you uploaded and found that it worked as you said. It is strange that it didn't work on the original table when the version you modified came from it. Since your version does work, I then adjusted the values in it back to the original values and everything is working fine now. Thanks so much. Inserting at the top allows me to easily use either the Windows app or the Android app to view and to add new data. I wonder why my original table would not auto-fill using the new formulas?
