Chart Questions

[harddrive747]

I have a couple of questions about charts. The first one is about being able to put in text in the "range" fields for names of series. For instance, I have a series of data that I want to call "unit members." I want to be able to just type that into Range for Names "unit members," and not have to select a cell or range. Can I do that?

The second question is a bit more complicated. In Microsoft Excel, I was able to create names for ranges of text. I see that the names came into Calc without a problem. In Excel, I'm able to use the those names to in the charts and this way I can have them automatically updated. I noticed in Calc, that it doesn't give me that option or allow me to use the names Is there a way to have the charts automatically updated?

Thanks for letting me know.

[acknak]

It seems that Chart does not support named ranges: Issue 64604: Allow Calc DataProvider to handle named ranges (new chart).

You can register there and add a vote (up to two) or a comment.

The current target for adding the feature is "3.x" and 3.0 is planned for September, so it doesn't look like this will be added anytime soon.

I'm not sure what you mean by "... Is there a way to have the charts automatically updated?" The chart is automatically updated to display the data in the ranges. You don't have to do anything special. If the data change, the chart will change as well. Is there something else you want to update?

[harddrive747]

Thanks for the information about name ranges in the charts.

With regards to the charts automatically being updated, let me try to clarify what I'm talking about. I work with data that continues to come in on a monthly bases. I have been tracking this data for the last seven years and what I want to do is to have the chart update automatically to reflect new data each time I add a new month. In Microsoft Excel, I use the "OFFSET" command to count the number of rows or columns that have data in them. Then when a new month comes up, I am able to add the new data to the row or column and the chart will automatically add the information. Otherwise, I have to update the chart each time manually to reflect the new month, which given the number of charts that I work with to be very time consuming.

So I will give you the command that I use in Excel to accomplish this in the name section
Length_of_Months=12
=OFFSET('Stake Totals'!$B$1,0,COUNTA('Stake Totals'!$1:$1),1,-MIN(Length_of_Months,COUNTA('Stake Totals'!$1:$1)))

This is why I asked about the named ranges, because that is how I do it in Excel. I use the above name range to set up the series values and axis labels.

Thanks for letting me know. Also, what about just adding text into ranges, instead of using cells, which is a way to do it.

[acknak]

Well, Calc has an OFFSET function, but I can't say if your same technique will work with Calc. I would expect it to require some effort to get it to work. In my (limited) experience, Calc can handle simple sheets from Excel with only a little tidying up. More complex sheets take a lot more work. Calc tries to be compatible with Excel, but it is not a drop-in replacement.

BTW, is that an exact copy of the command you use in Excel? Because there are several aspects of what you showed that I don't understand.

... what about just adding text into ranges, instead of using cells, which is a way to do it.

Sorry again, I don't understand what this means.

[Villeroy]

This technique of dynamic references is supported with conditional formatting and data validation by cell range (since 2.3). Surprisingly the new charting module can not handle this type of referencing.
Gnumeric is another free, mult-platform alternative to Excel which can do this indeed.

[acknak]

Oh, you mean that you can enter that formula as the data range in the chart configuration? That's pretty slick.

I wonder how OOo would handle that, since the Chart component is completely separate from the spreadsheet. Would Chart have to have it's own formula parser/compiler? Hopefully not. Is there a way to pass that over to Calc and get back a sheet area reference?

[Villeroy]

Since the charting module is part of the office suite, there should be a formula parser "at hand". On the other hand charts are independent part of the ODF standard, so dependency on spreadsheet formulas would break the ODF standard.
However, if you insert/remove cells from your chart table rather than append/clear contents the chart range will be adjusted like any other reference in formulas, database ranges and named references.

[Villeroy]

Just uploaded a Python module to handle insertion/removal of rows in the most easy fashion:
http://user.services.openoffice.org/en/ ... =21&t=2350