Issue 64604

Summary: Allow Calc DataProvider to handle named ranges (new chart)
Product: Calc Reporter: bjoern.milcke
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: belugue70, bgrupczy, discoleo, IngridvdM, issues, jeffooo, laconicolas, oukcha.fr, pagalmes.lists, rb.henschel
Version: 680m152   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: ENHANCEMENT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
Chart based on OFFSET function
none
Chart xls with OFFSET function
none
Chart xls lost OFFSET function none

Description bjoern.milcke 2006-04-21 10:11:28 UTC
The methods createDataSource() and createDataSequenceByRangeRepresentation()
should be able to deal with named ranges given as range representations (for the
former method in the argument "CellRangeRepresentation").

->NN: Do you have an idea how complicated this would be? (like "impossible",
"very hard", "medium", "easy", "no-brainer")
Comment 1 Regina Henschel 2006-04-21 11:54:36 UTC
I add a hint to issue 4967, which is an old RFE for that feature.

Excel 2002 does it in that way, that it allows you to enter a named range, but
than immediately chance it to the address range which is referenced by the name.
If you go back one step in creating, you can see that. So that is no good way.

OOo should handle it that way, that the name of the named range is stored with
the chart, so if you alter the addresses which are referenced by the name in
Insert - Names - Define, the chart will use the new data without any change on
the chart settings. That way would be good :)
Comment 2 IngridvdM 2006-04-22 14:42:29 UTC
changed summary to not start with 'new chart:' as this is taken as indicator for
regression bugs in the chart reimplementation
Comment 3 villeroy 2006-06-05 02:39:30 UTC
If it could be implemented as regina suggested, then it would be possible to
assign dynamic named ranges like "chartRange"
=OFFSET($Data.$A$1;0;0;COUNTA($Data.$A$1:$A$1000))
If this would involve database ranges as well, then we could get adjusted charts
from refreshed import ranges.
If this conversion of name to actual range would be implemented by some generic
interface, we could have dynamic print ranges and other features (data pilots)
that  currently require manual resizing of a range.
Comment 4 niklas.nebel 2006-07-07 19:23:05 UTC
changing target
Comment 5 bjoern.milcke 2007-07-30 17:27:03 UTC
I don't see why we still need Issue 4967. If the Calc data provider supports
named ranges, I think we are done. So I'll close that one (though it is much older).
Comment 6 bjoern.milcke 2007-07-30 17:27:44 UTC
*** Issue 4967 has been marked as a duplicate of this issue. ***
Comment 7 bjoern.milcke 2007-07-30 17:29:31 UTC
*** Issue 4967 has been marked as a duplicate of this issue. ***
Comment 8 Martin Hollmichel 2007-11-09 16:52:33 UTC
change target from 2.x to 3.x according to
http://wiki.services.openoffice.org/wiki/Target_3x
Comment 9 tlmeur 2009-07-29 10:28:35 UTC
Hello,

I need this functionality and I wonder what is this issue status ?
Is it planed to fix it ?

I noticed that Gnumeric has implemented it

Thank you !
Comment 10 jean_francois_philip 2009-08-25 09:07:33 UTC
"OOo should handle it that way, that the name of the named range is stored with
the chart, so if you alter the addresses which are referenced by the name in
Insert - Names - Define, the chart will use the new data without any change on
the chart settings."

+ 1

With this possibility, I can create dynamic range with OFFSET function.

For example :

Insert > Names > Define 
 - Name : MyRange
 - Assigned to : =OFFSET($A$1;0;0;COUNTA($B$1:$b$10);2)

Insert > Chart > DataRange : MyRange
Comment 11 bnordgren 2009-09-04 20:17:11 UTC
"OOo should handle it that way, that the name of the named range is stored with
the chart, so if you alter the addresses which are referenced by the name in
Insert - Names - Define, the chart will use the new data without any change on
the chart settings. That way would be good :)"

+1 

I was just trying to do this for a calibration spreadsheet template.  I wanted
to set up the equations and charts to take named ranges. Then all I would have
to do in the future (when I pasted in the real data) is adjust four or five
ranges and have everything work.  Does the OpenDocument Formula spec allow this?
Perhaps we should hit up Oasis about this.
Comment 12 bnordgren 2009-09-04 21:44:32 UTC
Errata: the relevant spec above is the OpenDocument spec proper, not
OpenDocument formula.  

I just reviewed ODF v1.2 cd3 with this question in mind.  It seems ambiguous
whether named ranges are allowed or not.  I sent a query to the office-comment
list.  Please see http://markmail.org/message/6ehmoq5nvodojdak

If the TC responds that the definition in section 8.2.1.4 excludes named ranges,
perhaps the way forward is to write a new feature proposal and submit it to the TC. 

However, it seems like the resolution of this problem lies with those who
control the file format and not those who write the application.
Comment 13 harddrive747 2009-09-06 23:18:06 UTC
I have tried to insert a name range into a chart and Calc will not allow me to
use a name range.

When I try put it in, the box turns orange and I can't do anything.  I would
love to be able to use a name range to be able to dynamically update a chart.

So let me know what I may doing wrong.  I have followed and used the example
from jean_francois_philip and it doesn't work.

So any help would be greatly appreciation.
Comment 14 jeffooo 2012-11-27 20:40:00 UTC
What's the progress on this issue, please ? :)
Comment 15 Oukcha 2014-02-22 09:14:13 UTC
Created attachment 82663 [details]
Chart based on OFFSET function

advantage of having a name for a diagram.

Now, we must use intermediate data range
Comment 16 Oukcha 2014-02-22 13:59:48 UTC
Created attachment 82668 [details]
Chart xls with OFFSET function

Testing with (very old !) Excel 2000, Excel accepted a name based on OFFSET function for data range to create chart
Comment 17 Oukcha 2014-02-22 14:04:02 UTC
Created attachment 82669 [details]
Chart xls lost OFFSET function

Always on Excel 2000, Excel lost name, range named data is replaced by text, so is not dynamic on Excel.

If this issue is resolved, AOO can have a dynamic data range for created chart.

Best regards
Comment 18 jeffooo 2014-03-17 12:35:12 UTC
(In reply to Oukcha from comment #17)

Excel 2003, Excel 2007, Excel 2010, Gnumeric 1.10.17 accept dymanic range with OFFSET function.
Comment 19 Nicolas 2015-11-23 14:07:32 UTC
Hello, 

I wish I could handle an X-axis that changes with the datas (not only with an X/Y chart)

Dream with me
Comment 20 Marcus 2017-05-20 10:45:17 UTC
Reset the assignee to the default "issues@openoffice.apache.org".