How to get charts and formulae to update when appending a new row to my data.

[Entropy]

I have a spreadhseet with 3 tabs. One is the data, one is charts, and one is summary. Right now, the data ranges on the charts and the formulae on the summary tab all are from row 4 thru 19, since that’s where the data is. But as I add rows, the charts and formulae don’t pick them up since they only aim at up to 19.

When I set the chart’s data range to go to 20, it includes the empty row in the chart. So I can’t just pad it out and slowly fill it, because the chart doesn’t seem smart enough to realize there’s no data there.

So how can I set this up so that all I have to do each time I have a new entry is to add in new data to the row and my charts and formulae update accordingly?

[erAck]

One possibility is to activate Tools → Options → Calc → General, Input Settings, Expand references when new columns/rows are inserted; however, you have to insert rows below the existing range to append data. This is the cleanest looking and calculating method though.

Easier may be to have one excess row at the bottom and when adding rows insert them above that row so the references get adjusted. That also doesn’t interfere like the “expand references” as sometimes expanding a reference when inserting columns/rows immediately at the edge of references is unwanted.

Depending on the functions used, if they are simple like SUM(), AVERAGE(), MIN(), MAX(), … you could also replace them by AGGREGATE() function calls (see its help), which can be told to ignore hidden rows, then you can add excess rows in the data range and hide them, then Chart doesn’t display them as empty but skips them (unless you tell it differently). You’ll have to unhide/show a row though each time when adding data, which is not very practical.