[Solved] Automatically Updating chart in OpenOffice.org

[Irish Souffle]

Background: I'm attempting to track sales and I am making a chart of this in OpenOffice Calc. I am running OpenOffice 3.1.1, which I believe is the latest released version, since 3.2 is still in the RC stage. My chart has two columns; the first is a list of dates, the second is a list of income values for each day. I've made a graph of this in a second sheet.

Here's my problem: when I add a new entry, the graph does not automatically update. I have to edit it, select the new row, and only then does the graph update. Is there any way to make it automatically update?

I've found some solutions for Excel, but they don't seem to work. I used the ideas from this page but ran into issues because OpenOffice does not display a "series" item in the formula bar when you select the graph.

Searching with Google did not help much either. I also tried making the changes in Excel on another computer and then opening the file back up in Calc. This, however, just causes Calc to display a blank graph.

The chart is in the same spreadsheet as the data, just on a different page. If I change the data, the chart does update. The issue is that when I add new data (new rows), the chart does not update, because it has been given only a certain range of cells to include as part of the chart. For instance, right now I have 14 rows of data. If I change anything in any of those rows, the chart will update. However, if I add data in the 15th row, it will not. I tried including all the cells up to the 65,536th row as a workaround, but it ends up interpreting empty cells as zeros, which ruins everything.

The only relevant topic I found on here was this one, which is 2 years old.

[Villeroy]

This is because a spreadsheet has no concept of tables. There are rectangles of cells which behave like a table if you organized them as tables. When you insert new rows into your list, all references will adjust to the new size, including the chart ranges. Same when you delete rows.

Using a database to store the raw data would avoid this problem among some other typical spreadsheet problems. The spreadsheet would import a clearly separated table from a first row until the last data row and Calc can adjust growing and shrinking import ranges.

[tinkertron]

Villeroy I don't mean to throw in any confusing matter, but couldn't he have someone do a VBA / Macro at will do the automactic updates for his graphic? The reason I say this is cause I had someone do one up for me and it work like a charm. I don't know VBA / Macro programming, heck i bearly know formulas, but I know it can be done. Is it wise? Probly not, but it does work.

Irish Souffle - I agree somewhat with (Velleroy) even though we hate each other, but that aside, you should uses a database software, or even have a php/mysql programmer to design you one up on the web. I have completed my spreadsheet on the way I want it to act, with formula and all, and now I will be turning it over to a php/mysql programmer to design it and add it to the web, with add/edit/delete funcation

[Villeroy]

You really have some coding slave able to write some listener that updates each and every reference to some range whenever some untrained user appends data to some range? It really updates all the references in formulas, charts, named ranges, conditional formats, data validation and form controls wherever you append some data to a list? Wow. That would be amazing, indeed.
I can only recommend to learn using the applciation instead of hacking on it. Blind use of macros (written by "someone") are a massive problem for compatibility and reliability of documents.

[Irish Souffle]

Or I could go back to using Excel, which allows me to do what I want quite easily.

Thanks for the help.

[Villeroy]

Really? Good bye then! Read you on the Excel forums.

[PeterButler]

Feature request?

Most of the time when I use graphs I *want* them to auto update when anything is changed. Updating when something I change does not change the graph is generally not a problem.

Best of both worlds is a setting, global or specific to each graph, which enables or disables automatic “if anything is changed” graph updates. I would rather NOT go back to Excel.

[Bear_Ona_Bike]

I'm having the same issue.
I use the graph to show suspension characteristics and I've noticed it doesn't update with a new set of figures.
The only 'fudge' I've found to work is to reselect or change the range in the graph which forces it to recalculate.
i.e. I change the range from "$Sheet1.$A$2:$A$14" to "$Sheet1.$A$2:$A$13" and then back to "$Sheet1.$A$2:$A$14"

Is there not an automatic way to do this?

[d_vineet]

The trick is to define a large data-range.
e.g. You have the data in cells from A1 to B200 at present.
Specify data-range as A1 to B1000.
Then click & stretch the chart-area.
And that's it. As the data is added to the range, the chart will reflect the addition.

HTH

Cheers,
Vineet

[SimSportPlyr]

I hope it's ok if I ask a question on this subject.

Maybe I misunderstand d_vineet's reply......

If I have already defined my chart, is there a way to modify the chart so that it reflects the new data points?

I don't see a way to redefine the data-range, but maybe I'm overlooking something basic.

Thanks.

[Alex1]

Rightclick on the chart and select Edit, then rightclick again on the chart and select Data Ranges. Edit the range or click the button to the right of it and select the range with the mouse.

[rgarrett]

I think I've found a good way to automatically update charts without a macro.

First, add a row to your spreadsheet, where all the cells have this formula:
=INDIRECT(ADDRESS(ROW()-1;COLUMN();4))
meaning, show the same value as the cell in the row above this one. Then, redefine your chart Data Series ranges to include this new last row. For me, the chart appearance doesn't change when I include this row.

When you want to add a new row of real data, insert a row before this new "copy row". At this point, you'll have an empty inserted row and all zero's in the "copy row". Fill in your new data into the inserted row, and the "copy row" will be automatically updated to those values. The chart ranges will be automatically updated to include the new position of the "copy row", and everything in the chart looks right.

[Villeroy]

The problem with this topic was that nobody ever gave an example for a non-updating chart. My charts do update well. They are either bound to database ranges or I work with insertion of cells which updates nor only charts but all references in one go. Same as in Excel, by the way.

=INDIRECT(ADDRESS(ROW()-1;COLUMN();4))

contributes nothing. It i just a very convoluted way to implement relative references.

[david7365]

I think the best answer is that spreadsheet programs all imitate the limitations of the earliest spreadsheet programs, as if that were a good thing (to provide exact feature compatibility). So, the truth is that no spreadsheet program will automatically adjust the data series range of a chart as new data is added to its series rows or columns. My suspicion is that this is a common problem which no spreadsheet developer will fix, even with an extension enabled by an explicit preference/option. As a retired software developer myself, I am ashamed of such avoidance of improving the functionality of software, leaving users to post questions that get no good answers until they give up.

[Villeroy]

Reference A1:A99 must not update just because you enter something in A100. That behaviour would turn the spreadsheet into a much worse flea circus than it has been ever since.
In order to expand a reference A1:A99 by one row you've got to insert a cell. This "traditional" makes perfect sence in this environment of empty cells.
A new type of software that implies a certain structure with confined lists ranges, clear data types, data separated from calculations etc. would be more like a database, less "flexible". People hate databases. People want Excel compatibility and they want to mess around at will without plan nor structure.

[Lupp]

@david7365 : I really wouldn't like Charts pretending even more to know what I want or "need". Ok, designing a chart in every detail from scratch wouldn't be fun. Some help is welcome. But to define the ranges is my privilege I would insist (including inserted rows is well accepted).

A rather simple (on the surface) way to solve the issue should be to allow for calculated data ranges using OFFSET() - as validation ranges already do. Whether rows and columns should be handled the same way or not may be left to further discussion.

[Hatstand]

@Villeroy - The problem with this topic was that nobody ever gave an example for a non-updating chart.

Here's an example. I'm tracking my energy usage by reading my electricity meter each month. So I have a spreadsheet that includes:

Code: Select all

Date         kWh
01/12/14     103
01/01/15     202
01/02/15     305

Each month I add a row after the last one with the most recent reading. (Note my actual spreadsheet is more complicated than this, but I'm simplifying.)

I've created a graph for this data, but each month I have to update the end of its data range because this doesn't happen automatically; I'm always adding not inserting. (As an aside, I arrived on this thread because I was looking for a way to stop my chart being updated while I am changing the data ranges in the dialog as this often seems to mess up the chart, particularly the scales, if I don't do it quickly enough. I just want it to update when I OK the dialog. However rgarrett's trick means I can avoid updating the data ranges manually.)

@Villeroy - It is just a very convoluted way to implement relative references.

While it is a relative reference, it isn't equivalent to how simple relative references work. For example, if we have:

Code: Select all

    A
1  10
2  15
3 =A2

If you insert a row between A2 and A3, the reference in the now A4 will remain A2, but for rgarrett's trick to work it needs to be A3. However it is convoluted because a simple OFFSET can be used:

Code: Select all

    A
1  10
2  15
3 =OFFSET(A3,-1,0)

This isn't an ideal solution because it means there's duplicate data in the graph. A way to turn this into a feature would be to make it possible to specify an excluded data range in the chart. So above we could have the included range be A1:A3 and the excluded range A3:A3. Users could then just enter the text "Insert row here" into A3.

[david7365]

Your explanation seems very clear, but it is missing information about how to actually make the series definition in the chart update automatically as new information is inserted. Without this final piece of information, this posting is as useless as all the other attempts to solve this common problem, which reflects a poor design decision in all spreadsheet products.

[Villeroy]

Simply insert new cells and it will update automatically. No further interaction required, except for:
Go to Tools>Options>Calc>General and check the "expand references ..." option. Now you can also insert directly adjacent to the existing reference and the ref will expand.

[david7365]

When I tried to set up the spreadsheet, the cell containing "=OFFSET(A3,-1,0)" is displayed as "Err:508".

[david7365]

That error happens even if the checkbox in Options is set. And I still don't understand how to specify the chart. What are the series settings?

[Arigamor]

Dedicated to another newbie like me.

Well, I know this post is very old and I know is "Solved", but looking for a solution for my problem I got here and it seems that there wasn't a solution .
After several days and tries, I think I found a solution for the issue with a macro of my own.

I have 150 sheets in a calc document, every one with its own chart, LineDiagram and a Bar graph. Both graph take the same range of data to display. One or more sheets can be updated daily or not, the cells are ordered up/botton by date, so the first row contains the later data. And... No way, I can not "update" graphs one by one manually after locating which one was changed.

Of course, when inserting a new row, Calc re-ranges the graphs, forgetting to include the newest row. Here my solution, maybe it could be useful for someone else.

1) Keep the "old" Chart.Ranges in an object variable
Sheet.Charts.Chart.getRanges()
2) Keep the "old" DataSerie in an object variable
EmbeddedObject.FirstDiagram.CoordinateSystems.ChartType.getDataSeries
3) Remove the Series
ChartType.removeDataSeries(idx)

4) Insert the new row

5) Set the Ranges saved in step 1
Chart.setRanges( oldRanges)
6) Set Data Series saved in step2
ChartType.setDataSeries( oldDataSerie)

You will have also have to set some values in your XAxis and Yaxis if you have customized the graph.

I havent tried deleting a row because didnt need it. But it does work for inserting.