Excel not auto-filling formulas in an Excel Table

[wcstarks]

I have a table sorted in descending order by date. I need the most current data at the top because the table will eventually contain several thousand rows when I insert. I add new rows at the top for the current data. I created a normally hidden dummy row 2 to maintain formatting. I also created the two formulas there and replicated them down the two columns. When I insert a new row 3, the formulas do not auto-fill in the inserted row. How do I get these formulas to auto-fill so I do not have to do it manually with each new row insertion?

Before I inverted the sort order (and adjusted the formulas accordingly) the formulas auto-filled properly with each new row added at the bottom of the table. The header looks like this with the formula for column E showing in the formula bar:

Column F is similar: =F3+D2

I might mention that the formulas are omitted from the last row because they would reference beyond the the last row. I just have values in the last row.

[#2]

@wcstarks The way you work with structured tables defeats their purpose and strength as you have discovered. Best to add new rows at the bottom and keep the sorting by date in ascending order. That way you can easily write structured formulas that sum the running totals for columns E and F avoiding direct cell references. Such direct cell references to 'the row below' will break as soon as you insert a row. 

And if you need/want to see the latest entry at the top of the table, why not add a row above the table headers that pick-up the values from the last row in the table.

I've created an example of such a table in the attached file.

[#3]

@Riny_van_Eekelen 

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?

[#4]

@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.

[#5]

@wcstarks 

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.

So I'm the one who helped  you with that spreadsheet a year ago (reading the exchange here between you and @Riny_van_Eekelen reminded me of that exchange). I'm going to let my friend Riny continue to help here, but I do want to make a suggestion: Separate input from output.

In particular, you seem to be emphasizing needing to see the most recent entries, which is why you enter them at the top (this was part of last year's exchange too).....but we keep saying that tables work better if you enter new data at the bottom,  This "expert approach" is actually better when you realize that all you're doing at the "input" end of things is collecting raw data. What you do with it--the "output"--is best separated, dealt with by some form of "dashboard" sheet, separate from the input (although still in the same workbook), where you can display whatever relevant data you need on a realtime basis, using FILTER and other tools to extract the most recent, most relevant from the raw historical records. 

[#6]

@mathetes 

Thank you for your help in the past. The Diabetes table designed with a lot of help, that I have been using for some time, has worked well for me inserting at the top in descending order. I suppose your recommendation is a better approach, but I would not know how to implement it. That is well beyond my limited skills.

[#7]

I'll see if I can take a look at the sheets you've attached later, then. What I'm suggesting isn't really all that difficult; it's maybe trickier when first doing it (involving a different conceptual framework), but in the long run easier to use and maintain.

[#8]

@mathetes 

I thought I had attached the actual Excel table, but I don't see it now. So, I will upload it. Most of the data is dummy data to conceal the actual amounts, but the table is otherwise an correct copy of the actual table. The formulas are in columns E and F. Thank you in advance. Again, row 2 exists only to preserve formatting when inserting next to the header. I suppose it will no longer be needed. The table will grow to several thousand rows. I hope I wouldn't need to navigate to the bottom each time I enter raw data. Avoiding that is what makes inserting at the top seem so natural.

[#9]

@wcstarks 

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.

[#10 · Accepted solution]

@mathetes, @wcstarks 

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.

[#11]

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?

[#12]

@wcstarks Glad it worked out, but obviously can't be sure why it didn't work at first without seeing your original file.

[#13]

@wcstarks 

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]C

that will  avoid inconsistent formulas.

[#14]

Thanks for taking the time to respond to my question. I now understand that one cannot use direct cell references effectively in tables. I am not sure I understand the "defined name" approach you suggest. However, Riny_van_Eekelen provided a solution for inserting at the top using column names, which I do understand and is convenient for me to use both in Windows and on my Android phone. I appreciate everyone's attention to my question. Thank you all.