Automatic chart data range adjustment - is it possible?

users
#1

Every day, I add another row to my spreadsheet. This row's data must be
included in an existing chart, so the data range must grow by one row every
day. (The data hardly matters - if it helps, one example would be a record
and graph of all daily maximum temperatures since date X.)

In Excel, I would specify a table and create the chart from the table. Then,
adding a row to the table would automatically regenerate the chart to
include the new data.

Is this possible in LibreOffice?

Up until now, my workaround was to specify an excessively large data range,
cheerfully add my data once per day, and just update the range whenever
reality caught up with it.

Alas, a few days ago I finally updated to Precise Pangolin (or whatever it's
called), and this installed an update to LibreOffice which "helpfully"
rounds down my deliberately inflated data ranges. If I could stop
LibreOffice doing that, that would be an acceptable solution.

Any ideas much appreciated.

#2

Hi Richard,

Richard Heathfield schrieb:

Every day, I add another row to my spreadsheet. This row's data must be
included in an existing chart, so the data range must grow by one row every
day. (The data hardly matters - if it helps, one example would be a record
and graph of all daily maximum temperatures since date X.)

In Excel, I would specify a table and create the chart from the table. Then,
adding a row to the table would automatically regenerate the chart to
include the new data.

Is this possible in LibreOffice?

Yes, the trick for it is a very old one.

You have to know, that the range of the chart is adapted, if you insert a row not at the end but inside the existing range. So you have to insert the values of the next day _inside_ the existing data range. But logically you have to add them. The trick is, to add a dummy row as last row and each day add your new data just above this dummy row. Click on the dummy row or mark all dummy cells and use Insert > Row or Insert > Cells respectively.

When you work with an xy-chart, you can write a text into this dummy row to remind you of the purpose of that row.

When you work with a category chart, this dummy row needs at least one entry in the category or in the value, even an empty string will work. But I would use a reminder text like "add data above" in the value column here too.

Kind regards
Regina

#3

Regina Henschel <rb.henschel <at> t-online.de> writes:

Hi Richard,

Richard Heathfield schrieb:

<snip>

>
> In Excel, I would specify a table and create the chart from the table. Then,
> adding a row to the table would automatically regenerate the chart to
> include the new data.
>
> Is this possible in LibreOffice?

Yes, the trick for it is a very old one.

<snip>

Oh deary deary me. My reply, like Gaul, divides into three parts:

(a) "How stupid of me not to have thought of that" - Thomas Huxley
(b) "How stupid of LO to require that" - Moi
(c) "Thank you" - Moi aussi

Cheers, Regina, you're a star.

Regards
  Richard Heathfield

#4

Richard/Regina:

After reading this, I recalled something I had seen in the Options Dialog:

Tools>Options>Calc>General>Expand references when new columns/rows are inserted

With this enabled, if you go to the select next empty row below your data, then insert a row, it expands the chart reference.
This works with Version 3.6.7.2 (Build ID: e183d5b), Version: 4.2.4.2 Build ID: 63150712c6d317d27ce2db16eb94c2f3d7b699f8 and Version: 4.3.0.0.beta2 Build ID: a06aa316117a6ff0f05c697c82831c227812d810, all on Vista.

Sincerely,

TomW

#5

TomW <tomwebb <at> fairpoint.net> writes:

> Richard Heathfield schrieb:
>> [...]
>> In Excel, I would specify a table and create the chart from the
>> table. Then, adding a row to the table would automatically
>> regenerate the chart to include the new data.
>>
>> Is this possible in LibreOffice?
>
> [...] The trick is, to add a dummy row [...]

After reading this, I recalled something I had seen in the Options Dialog:

Tools>Options>Calc>General>Expand references when new columns/rows are
inserted

With this enabled, if you go to the select next empty row below your
data, then insert a row, it expands the chart reference.
This works with Version 3.6.7.2 (Build ID: e183d5b), Version: 4.2.4.2
Build ID: 63150712c6d317d27ce2db16eb94c2f3d7b699f8 and Version:
4.3.0.0.beta2 Build ID: a06aa316117a6ff0f05c697c82831c227812d810, all on
Vista.

Hi TomW. Thanks for giving this some thought. I looked, and that option is
indeed available in my version of LO. The problem with the dummy record
solution is that you have this dummy record on the end of your data... and
the problem with your solution is that you have to remember to *insert* a
row rather than just add one... which means writing a reminder there, which
means you effectively have a dummy record anyway, but one that isn't
included in the graph data. So it is a marginal improvement on Regina's
suggestion. A better solution would be for LibreOffice to support tables.

From what I've seen in my tour around various support channels, though, it

may be that LibreOffice people think "table" means "a word-processory sort
of grid thing", rather than "fantastically powerful spreadsheet concept". Ah
well.

Anyway, yes, that's an improvement, and I'll adopt it immediately. Thanks!

Regards
  Richard Heathfield