Issue 29848 - copied sheet: chart-copy references original data instead of copied data
Summary: copied sheet: chart-copy references original data instead of copied data
Alias: None
Product: General
Classification: Code
Component: chart (show other issues)
Version: 3.3.0 or older (OOo)
Hardware: All All
: P3 Trivial with 25 votes (vote)
Target Milestone: ---
Assignee: kla
QA Contact: issues@graphics
Keywords: ms_interoperability, rfe_eval_ok, usability
: 23212 59775 61499 85396 92580 101581 109897 113289 (view as issue list)
Depends on:
Blocks: 72764
  Show dependency tree
Reported: 2004-06-03 22:30 UTC by bettlerthomas
Modified: 2017-05-20 11:41 UTC (History)
9 users (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---

patch_20090921 (3.60 KB, patch)
2009-09-21 05:00 UTC, weiz
no flags Details | Diff
example showing the problem (20.54 KB, application/vnd.oasis.opendocument.spreadsheet)
2009-09-24 13:14 UTC, IngridvdM
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description bettlerthomas 2004-06-03 22:30:26 UTC
I get a unexpected reaction... 
Steps to reproduce... 
1. Enter some simple data (one cell is enough) 
2. Draw a chart on the same sheet  
3. Copy this sheet 
4. What you get is not what you might expect 
You might like the new chart using the new sheet as source. But instead both chart use the same (old) 
source. The datasource reference should not be held absolute, but relative to its own sheet
Comment 1 frank 2004-06-04 10:16:38 UTC

wrong component, I corrected this to Chart,

Comment 2 kla 2004-06-08 13:53:00 UTC
tk->bh: i think both should be possible.
Comment 3 kla 2004-06-08 13:54:19 UTC
Hi Bettina,
one for you.
Comment 4 bettina.haberer 2004-11-29 18:19:35 UTC
Reassigned to Ingrid.
Comment 5 IngridvdM 2005-04-19 14:17:18 UTC
->BM: Please take care of this Issue. I also think that the current behaviour is
not a good default.
Comment 6 bjoern.milcke 2005-04-19 16:11:39 UTC
It looks like Calc is able to convert ranges from the source sheet to the new
sheet (like $Sheet1 stays $Sheet1 while Sheet1 becomes Sheet4 - or whatever the
new sheet is called), so it should be possible to change all affected ranges a
chart uses. Currently (in the new chart) all ranges are written with $s, but we
should change this anyway, because otherwise users would never get notice if the
underlying range of a series is moved or not.
Comment 7 frank 2006-01-31 15:05:44 UTC
*** Issue 59775 has been marked as a duplicate of this issue. ***
Comment 8 lars 2006-02-02 15:35:33 UTC
*** Issue 61501 has been marked as a duplicate of this issue. ***
Comment 9 lars 2006-02-02 15:37:54 UTC
*** Issue 61499 has been marked as a duplicate of this issue. ***
Comment 10 Rainer Bielefeld 2008-01-20 08:13:22 UTC
Still see this in 2.3.1
Comment 11 Rainer Bielefeld 2008-01-21 06:26:21 UTC
*** Issue 85396 has been marked as a duplicate of this issue. ***
Comment 12 eightize 2008-04-24 20:13:09 UTC
Issue 64604 seems like it may be similar?
Still applies to 2.4.0.
Comment 13 Robert Pollak 2008-06-21 23:49:39 UTC
Ahem, bm: You switched the status to STARTED in April 2005. Have you really
started at that time? What is your state now? (I see that you currently have 66
issues of same or higher priority assigned - so this item probably is starving.)

If somebody else wants to take a try on this, could you describe what has to be
done and how much work you expect it to be?
Comment 14 bjoern.milcke 2008-06-26 17:02:27 UTC
->jondo: Well, we used STARTED as a synonym for ACCEPTED, which does not exist as a state in 
IssueZilla. So it just means we found it valid, but didn't really start with it. This is also the current state. 
(Meanwhile we would leave such isses on NEW).

Well the solution is not so straight-forward here. If you have Sheet1 with a chart referring to cells on 
Sheet1 things are simple when you copy Sheet1. However you might have references to other sheets or 
to both, the same sheet and another sheet mixed. What do you want to do then?

Automatisms like "when all references are on the same sheet, relink them to the new one" are not 
obvious to a user. So you would need some kind of user interaction. So, it is not so trivial to deal with 

If someone is interested in finding a solid solution this would be greatly appreciated.
Comment 15 bjoern.milcke 2008-06-26 17:05:42 UTC
Just an addition to the remark to hold the sheet references relative:

A chart uses ranges from Sheet 1, Sheet 2 and Sheet 3. It resides on Sheet 2. You copy Sheet 2 and 
append it as Sheet 4. What happens? Is the new chart pointing to Sheet 1 (stays as before), Sheet 4 (was 
Sheet 2) and Sheet 3, or are all references relative, i.e. the new chart refers to ranges on Sheet 3 (was 1), 
Sheet 4 (was 2) and Sheet 5 (was 3)?
Comment 16 bettlerthomas 2008-06-27 09:59:02 UTC
Well, this bug isn't to fix all the possible cases, just the most common ones.

- Many times you have a graph from data of the same sheet, so treat this
references relative. So on the copied sheet the graph should refer the copied data.

- To respond on your more complex case. Many people would not expect to change
any thing but the reference to the copied data. So the graph on sheet 4 should
refer to the data of sheet 1, 3 and 4 - IMHO.

Or more generally. Compare the action of copying a sheet to renaming a sheet.
If you rename a sheet you get the graphs references to that very sheet changed.
- If you copy it you might expect the equivalent behaviour for the graphs on the
new sheet.

Or does someone disagree with that?
Comment 17 Robert Pollak 2008-06-28 08:22:16 UTC
Thomas, I fully agree.
Btw., this is how Gnumeric behaves. I cannot test Excel at the moment, but it's
certainly the same there.
Comment 18 IngridvdM 2008-07-03 11:39:50 UTC
change owner
Comment 19 IngridvdM 2008-07-22 14:21:09 UTC
reset to new
Comment 20 Rainer Bielefeld 2009-05-06 05:57:26 UTC
*** Issue 101581 has been marked as a duplicate of this issue. ***
Comment 21 ypz88 2009-06-07 23:21:44 UTC
This issue still occur in version 3.1.
to jondo: Agree, Excel 2000 behaves same as Gnumeric.
Hope this will be fixed soon.. Thanks.
Comment 22 stephan66 2009-07-29 21:46:24 UTC
Bug is still there in 3.1.1-beta.

This is turning into one of the top annoyances of Calc-users at my office: 4
complaints in the last month (rising as the use of Calc is spreading beyond the
initial test group).

@iha: Is there anyone actively working on this bug?
Comment 23 IngridvdM 2009-07-30 09:18:09 UTC
I am not working on this issue. So if anyone would like to give it a try go-ahead!
Comment 24 ooo 2009-08-31 10:53:48 UTC
*** Issue 92580 has been marked as a duplicate of this issue. ***
Comment 25 IngridvdM 2009-09-03 10:07:13 UTC
@weiz, please have a look at this one. Thanks!
Comment 26 IngridvdM 2009-09-10 14:55:12 UTC
shifting target due to limited resources
Comment 27 weiz 2009-09-21 05:00:42 UTC
Created attachment 64875 [details]
Comment 28 weiz 2009-09-21 05:01:44 UTC
@iha, the patch is submitted, please let me know your suggestions. Thank you!
Comment 29 IngridvdM 2009-09-24 13:12:57 UTC
@weiz, the patch does only work for charts where the cell ranges are arranged in
standard way. More complex charts do break now while copying. I'll attach an
example to show the problem.
Comment 30 IngridvdM 2009-09-24 13:14:52 UTC
Created attachment 64930 [details]
example showing the problem
Comment 31 IngridvdM 2009-09-24 13:51:23 UTC
The methods used in the current patch do set a new rectangular range to the
chart. That destroys the former structure. Look at method
ScDocument::TransferDrawPage how the methods GetChartRanges() and
SetChartRanges() are used there. This is the correct way that will preserve the
structure. The method is called in case a sheet is moved to a different
document. After changing the code please also  make sure that the change
notifications are send correctly. If the values in the new cells change, the
chart should get updated. Something with the general update mechanism is broken
in dev300m59 so you might need to update to m60 if you are on that version.
Kind regards,
Comment 32 korsi 2010-03-05 13:31:40 UTC
Wouldn't this be a matter of getting the relative references to work properly. 
By removing the $sheet1 from the reference string in the chart and then copy the
sheet. The expected behavior would be that the reference still are relative.

How it works now is that the references are changed to absolute references when
copying the sheet.

Making this work would avoid all the possible conflicts with references to other
Comment 33 Rainer Bielefeld 2010-03-08 10:05:13 UTC
*** Issue 109897 has been marked as a duplicate of this issue. ***
Comment 34 IngridvdM 2010-03-08 15:33:34 UTC
I take back the issue to complete the fix.
Comment 35 IngridvdM 2010-03-08 15:43:19 UTC
Fixed in CWS chart43.
For copied charts the references are adapted as follows: Data References to the
source of the copy are changed to data references to the result of the copy. All
other data ranges remain unchanged.
Comment 36 stephan66 2010-03-09 10:22:30 UTC
Thanks a lot, Ingrid!

This greatly enhances the re-usability of charts in Calc. An important issue for
a lot of OOo "power users" at my company. 

I will test it as soon as the first builds with this fix included hit the net.
What build number will that be?
Comment 37 IngridvdM 2010-03-09 17:15:39 UTC
@stephan66, the exact build number is not known yet as the Childworkspace
'chart43' will go through a QA phase first before integration. But you can
observe the status of CWS chart43 here:
If the CWS is integrated the field 'Milestone (integrated)' will show the
version dev300mXX.
Comment 38 IngridvdM 2010-03-11 10:33:29 UTC
@Thomas, please verify in CWS chart43.
Comment 39 kla 2010-03-19 17:05:30 UTC
Seen ok in cws chart43 -> verified
Comment 40 niklas.nebel 2010-03-24 15:25:52 UTC
*** Issue 23212 has been marked as a duplicate of this issue. ***
Comment 41 ooo 2010-03-24 21:17:59 UTC
Although the declaration "Issue 23212 has been marked as a duplicate of this
issue." is not really fair (I reported 23212 on Thu Dec 4 20:25:00 +0000 2003,
i.e. is half a year earlier than 29848) - I am happy to hear that the issue will
be resolved after more than 6 years!
Thanks a lot, Ingrid!
Comment 42 rsking84 2010-04-01 20:24:00 UTC
I think a better way of phrasing this issue is that Chart components use
ABSOLUTE data references by default, so if you copy a chart to a new sheet the
data still pulls from the original place.

I would like to see chart objects default to RELATIVE references, and have the
option to toggle them to ABSOLUTE references using SHIFT+F4 as you can with
individual cells
Comment 43 IngridvdM 2010-04-13 15:00:36 UTC
A regression was introduced: Issue 110849.
Comment 44 stephan66 2010-06-03 11:02:22 UTC
Confirming that this problem is solved in DEV300m80 downloaded from (openSuSE 11.0, x86_64, 4 GB memory)

This solution is fine for now.

I agree with rsking84 that being able to use both relative and absolute
references in data ranges would make Calc's behavior more predictable. Calc now
silently changes relative references to absolute references without warning.

Defaulting to relative references would likely confuse and surprise a lot of
inexperienced users, though. Default should be absolute references.

It would make Calc stand out amongst spreadsheet programs (compared to Excel and
Comment 45 IngridvdM 2010-06-03 16:34:23 UTC
@rsking84 and stephan66, it would be nice if one of you could submit a separate
issue for the relative<->absolute reference issue and describe there what is
wanted. Thanks in advance!
Comment 46 IngridvdM 2010-07-29 16:11:36 UTC
*** Issue 113289 has been marked as a duplicate of this issue. ***
Comment 47 rsking84 2010-08-05 04:04:01 UTC
I have opened Issue #113664 as an enhancement to offer the ability to toggle
between absolute and relative references in Charts.