Dynamic range for chart

I have a chart based on a table containing 6 rows representing competitors and 48 columns representing laps completed during a race. The cells in the array contain lap times. I have also a separate cell containing the current maximum laps completed.

I want to be able to display a chart updated lap by lap in almost real time during the race. Clearly I could simply prepare a chart of the complete array, but during the early stages of the race there will be much more white space than data.

I had hoped to be able to define the data range based on the number of laps completed. I can do this in Excel using data ranges and OFFSET combined with COUNTA. I am tearing my hair out trying to get this to work with Libre Calc.

You may tell me that what I want is not possible without periodically editing the data range. I would love to think it was possible however and welcome any help that you can give.

It is important that this works with Libre Calc rather than Excel because it will be run on a raspberry Pi and incorporated into an Impress presentation. I am happy to provide a copy of the essential data from the spreadsheet.

Thanks in advance for your help.

‘Data Range’ and ‘Data Series’ of Charts do not accept calculated ranges in LibO (nor in AOO).
You may, however, enter much larger ranges than actually needed without spoiling the chart if you leave the uncharted range blank. (This at least for X-Y charts, I very rarely used different types.) But if you want to vary the ranges as taken out of larger ones filled with data, you will need to prepare your data for actual use in a helper range where you can use OFFSET. If charts allowed to use calculated ranges, they would need to do the same thing, just in a hidden way - and automated.

See alsi the related bug report tdf#64086.

In fact I cannot see a bug here, since I don’t know any specification, help text, or user guide telling named or calculated ranges should work. It’s just a “lack of feature”.

After all enhancement requests are treated the same way as bug reports for LibO. You may contribute to the linked one, find another one better matching your point, or file a new request to the bug site. (I also am thinking calculated ranges should be accepted. As I no longer use charts on a regular basis, I will leave action to others.)


Thank you so much for this. I have already spent considerable time trying to get this to work as it would in Excel. Now that I know it is not a supported feature I don’t feel so bad. I’ll just have to spend a little time editing the chart data range during the race. It’s such a shame because if this HAD worked then my Raspberry Pi would be seen as AWESOME! Now it’s only going to be INCREDIBLE!