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.
Thank you for responding. The community has been very helpful to me in the past. Just last year, someone helped me produce just such a table as you described. It works fine because all I need to know is the net result. See the Phonak attachment.
However, in this application, I need to be able to see the trends for the last week or so. I received help in the past in producing a similar excel table to help me manage my wife's diabetes. It does what I would like the Excel table of this topic to do. It is sorted in descending order and it auto-fills the formulas where ever I insert a new row at or near the top. As you can see, it allows me to see her trends for the last week or so. I have been using it for 3 years now and it has several thousand rows. I don't have the skill to figure this stuff out on my own.
I would like the table of this topic to do the same. I am under the assumption that one needs to use the structured approach to get Excel to auto-fill formulas in tables. I have tried using the column names in the formulas as is done in the attached Diabetes Mngr.jpg example, but have been unsuccessful. See attached Inv.docx.
Currently, when I add rows to this Excel table I need to copy and paste the formulas from the previous row. Otherwise, it works as needed. The VOT Amt column has a similar formula: F3+D2. The difference between the Diabetes table and the Inv table of this topic, is that the Diabetes table does not do a running total from one row to the next.
I would think it should be possible to get the Inv table to do what the Diabetes table does and still auto-fill the formulas. It is not possible?
wcstarks Sorry, but you have lost me already. You original post is about what seems to be amounts IN and OUT to calculate a running total balance of some kind. You refer to a diabetes schedule from some time ago. I remember seeing that one. And now you add a file (Phonak) that deals with streaming TV and phone calls and it has no formulas like the ones you describe.
So please, clarify what you need. in this particular case.