Download it now!
Bug 43175 - EDITING: Sheet references not correctly updated in charts when copying multiple sheets in Calc
Summary: EDITING: Sheet references not correctly updated in charts when copying multip...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Chart (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: Other All
: medium major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 51702 102910 (view as bug list)
Depends on:
Blocks: Chart
  Show dependency treegraph
 
Reported: 2011-11-22 12:32 UTC by Stephan van den Akker
Modified: 2020-01-10 14:24 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:


Attachments
Speadsheet with two sheets that both contain a graph (17.03 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-11-22 12:32 UTC, Stephan van den Akker
Details
The problem also happens when copying a single sheet with graphs. (34.00 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-03-30 12:40 UTC, snowboard975
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Stephan van den Akker 2011-11-22 12:32:21 UTC
Created attachment 53787 [details]
Speadsheet with two sheets that both contain a graph

How to reproduce:

1) Create a spreadsheet with two sheets
2) Put graphs on both sheets, referencing data from the same sheet
or:
1+2) Get the spreadsheet in the attachment
and then:
3) Select both sheets
4) Copy the sheets

Expected result:
The copied graphs reference data on the corresponding copied sheet.
This is the result for the active sheet (the one with the tab "on top")

Actual result:
Some of the copied graphs reference data on the original sheets.
This is the result for the non-active sheets.

Problem observed in:

LibreOffice Portable 3.4.3 - OOO340m1 (Build:302)
LibreOffice 3.4.2 - OOO340m1 (Build:1206) on openSuSE 11.4 (32-bit)
Comment 1 Stephan van den Akker 2011-12-21 04:00:55 UTC
Problem getting worse (!) in:

LOdev 3.5.0 
Build ID: 7362ca8-b5a8e65-af86909-d471f98-61464c4
tested on openSuSE 11.4 (32-bit)

Now both copies contain graphs that reference the original sheets.

Copying one sheet with graph at a time works as expected (copied graph references copied sheet).
Comment 2 sasha.libreoffice 2012-04-26 05:27:44 UTC
Thanks for bugreport
When I copy sheet1, appears sheet1_1 where diagram points to sheet1, but when I delete sheet1, it points to sheet1_1. Strange.
Comment 3 Cor Nouws 2012-05-10 06:11:35 UTC
(In reply to comment #1)

> Copying one sheet with graph at a time works as expected (copied graph
> references copied sheet).

Ah well, in daily version 3.6.0alpha0+ Build ID: 5c2c03e this results in a diagram with a separate data table, and not a reference to the data on the sheet..
Comment 4 Cor Nouws 2012-10-22 09:08:16 UTC
@stephan: this was correct in version 3.3.x ?
Then we should set as regression.
And also add it to a MAB, in my opinion.
OK?
Comment 5 Stephan van den Akker 2012-10-22 10:24:40 UTC
Very relieved to see the extremely serious problem in comment 1 and 3 tackled.

The problem in the original description never worked properly in OOo and LibO, so it can't be considered a regression.

In OOo 3.3.0 it started working correctly when copying 1 sheet at a time.

In my opinion it definitely is Most Annoying, so it qualifies for the MAB list.
Comment 6 sasha.libreoffice 2012-10-22 10:36:46 UTC
Reproduced in LO 3.4.0, 3.3.2, OO 3.2.1 on Mandriva 2010 64 bit
Steps to reproduce:
open attachment, select both sheet, copy, change cell B5 on First sheet for testing to which sheet points chart.
What is interesting:
delete first two sheet (two copied remains)
undo deletion
Now all 4 sheets point to first sheet (change cell B5 on first sheet to verify)

So, no regression
Comment 7 Cor Nouws 2012-10-22 11:15:45 UTC
@sacha, @stephan
thanks.

Before entering for MAB
- aren't there more related chart updating issues, that you know?
- isn't there (honest) a reasonable work around by doing sheet per sheet ?
Comment 8 Stephan van den Akker 2012-10-22 11:31:05 UTC
On your question of related Issues:

Although not involving charts, very probably technically related is:

https://bugs.freedesktop.org/show_bug.cgi?id=47662
EDITING: Inconsistent / unintuitive reference link handling for check boxes
and other form fields when Copying Sheets

IMHO The whole issue of how Calc deals with charts, form fields etc. on
sheets needs some serious attention. I imagine that the dev's time will be
spend most effectively when they deal with these issues simultaneously.

On your question of the reasonable work around.

Yes, this work around is do-able. Still annoying: Yet another work around
when doing work with LO.
Comment 9 Cor Nouws 2012-10-22 12:14:47 UTC
(In reply to comment #8)
> On your question of related Issues:
> [...]

thanks.

> IMHO The whole issue of how Calc deals with charts, form fields etc. on
> sheets needs some serious attention. I imagine that the dev's time will be
> spend most effectively when they deal with these issues simultaneously.

I guess so.
We/I can ask one of the devs what fits them best (contrainer, just issues, MAB, ...)

> Yes, this work around is do-able.

OK, then a MAB is not so logic, sorry.

> Still annoying: Yet another work around when doing work with LO.

Time for a MUW list ;-) (Most Used Workarounds :-\ )
Comment 10 Stephan van den Akker 2013-05-31 19:37:22 UTC
Reproducible in MASTER on Ubuntu 12.04 (32-bit):

Version: 4.2.0.0.alpha0+
Build ID: c2530b02311c46529eed53ee688bf6c83ce4b86
Comment 11 Stephan van den Akker 2013-11-14 20:01:17 UTC
*** Bug 51702 has been marked as a duplicate of this bug. ***
Comment 12 Stephan van den Akker 2014-07-12 08:24:02 UTC
Reproducible in master on openSuSE 12.3 (64-bit)

Version: 4.4.0.0.alpha0+
Build ID: 8b499cea76577b4221fccb17703aa9e86b625e90
Comment 13 QA Administrators 2015-07-18 17:43:23 UTC Comment hidden (obsolete)
Comment 14 Stephan van den Akker 2015-07-18 18:57:36 UTC
Reproducible in MASTER under OpenSuSE Tumbleweed (64-bit)

Version: 5.1.0.0.alpha1+
Build ID: 0a7375e372ee9583d31d44a7cc7b6a21e6197bf1
Locale: en-GB (en_GB.UTF-8)

It is already established that the bug predates LO (see comment 6). Setting the version field accordingly.
Comment 15 ryecomp 2016-06-16 02:03:45 UTC
When copying sheet containing a chart, for example, if i copied sheet 'A' to sheet 'B', all data reference about A must be replaced with B in new sheet.

But chart still refers to 'A' sheet data.

Is there any way to fix this libreoffice bug ?

I am using libreoffice 4.2.8.2 under Ubuntu 14.04.4 LTS
Comment 16 m.a.riosv 2016-10-01 21:31:50 UTC
*** Bug 102910 has been marked as a duplicate of this bug. ***
Comment 17 m.a.riosv 2016-10-01 21:33:38 UTC
*** Bug 102057 has been marked as a duplicate of this bug. ***
Comment 18 snowboard975 2017-03-30 08:54:29 UTC
Reproducible on Windows 7 (64bit)

Version: 5.2.6.2 (x64)
Build ID: a3100ed2409ebf1c212f5048fbe377c281438fdc
Comment 19 snowboard975 2017-03-30 09:07:09 UTC
This bug looks like a quite fundamental problem in the internal process of references of LiberOffice Calc. 
It degrades the reputation of LibreOffice Calc severely in my opinion. 
This bug has been reported six years ago, and still its state is 'not assigned'. 
I see the importance of this bug is marked as medium (major). 
Shouldn't its priority be changed to a higher one?
Comment 20 Stephan van den Akker 2017-03-30 10:03:16 UTC
(In reply to snowboard975 from comment #19)
> This bug looks like a quite fundamental problem in the internal process of
> references of LiberOffice Calc. 
> It degrades the reputation of LibreOffice Calc severely in my opinion. 
> This bug has been reported six years ago, and still its state is 'not
> assigned'. 
> I see the importance of this bug is marked as medium (major). 
> Shouldn't its priority be changed to a higher one?
Thank you for this endorsement.

Note however that:
MS Excel 2007 and 2010 exhibit the exact same behavior;
Gnumeric 1.12.23 seems to lack the ability to copy more than 1 sheet at the time.

So, saying it degrades the reputation of Calc may be a bit too strong.... 

It may help though if you add yourself to the cc list. This may influence the priorities of the devs.
Comment 21 snowboard975 2017-03-30 11:14:53 UTC
(In reply to Stephan van den Akker from comment #20)
> (In reply to snowboard975 from comment #19)
> > This bug looks like a quite fundamental problem in the internal process of
> > references of LiberOffice Calc. 
> > It degrades the reputation of LibreOffice Calc severely in my opinion. 
> > This bug has been reported six years ago, and still its state is 'not
> > assigned'. 
> > I see the importance of this bug is marked as medium (major). 
> > Shouldn't its priority be changed to a higher one?
> Thank you for this endorsement.
> 
> Note however that:
> MS Excel 2007 and 2010 exhibit the exact same behavior;
> Gnumeric 1.12.23 seems to lack the ability to copy more than 1 sheet at the
> time.
> 
> So, saying it degrades the reputation of Calc may be a bit too strong.... 
> 
> It may help though if you add yourself to the cc list. This may influence
> the priorities of the devs.

This bug not only happens when copying two sheets together, but also happens when copying one sheet only. 
You marked it as duplicate bug as in comment #17.
I tested your example worksheet with Excel 2017, but the same problem does not occur in Excel 2017.
Comment 22 snowboard975 2017-03-30 11:18:13 UTC
(In reply to Stephan van den Akker from comment #20)
> (In reply to snowboard975 from comment #19)
> > This bug looks like a quite fundamental problem in the internal process of
> > references of LiberOffice Calc. 
> > It degrades the reputation of LibreOffice Calc severely in my opinion. 
> > This bug has been reported six years ago, and still its state is 'not
> > assigned'. 
> > I see the importance of this bug is marked as medium (major). 
> > Shouldn't its priority be changed to a higher one?
> Thank you for this endorsement.
> 
> Note however that:
> MS Excel 2007 and 2010 exhibit the exact same behavior;
> Gnumeric 1.12.23 seems to lack the ability to copy more than 1 sheet at the
> time.
> 
> So, saying it degrades the reputation of Calc may be a bit too strong.... 
> 
> It may help though if you add yourself to the cc list. This may influence
> the priorities of the devs.

I understand LibreOffice is mostly developed by volunteers and have said the words in the hope that it will be developed better.
I'm sorry if my words sounded too strong.
Comment 23 snowboard975 2017-03-30 12:40:54 UTC
Created attachment 132282 [details]
The problem also happens when copying a single sheet with graphs.

Similar problem also happens when copying a single sheet with graphs. The copied graph looses its reference to the proper data table.

How to reproduce:
1. Download the attached ods file.
2. Copy the second sheet, Blad2, to the left side of the first sheet as its default new name, Blad2_2.
3. Copy the second sheet, Blad1, to the left side of the first sheet as its default new name, Blad1_2.
4. Copy the second sheet, Blad2_2, to the left side of the first sheet as its default new name, Blad2_2_2.
5. Copy the second sheet, Blad1_2, to the left side of the first sheet as its default new name, Blad1_2_2.
6. Click the second sheet, Blad2_2_2, and check the data range of the top left graph. Its expected value is $Blad2_2_2.$A$2:$B$7,$Blad2_2_2.$B$1. However, it points to $Blad2_2.$A$2:$B$7,$Blad2_2.$B$1.

I thought this is the example of bug reported in comment 17, but because it was marked as duplicate before, I post it in this thread. My test environment is as below.

OS: Windows 7 (64-bit)
LibreOffice Version: 5.2.6.2 (x64)
Build ID: a3100ed2409ebf1c212f5048fbe377c281438fdc

Should this be regarded as an independent bug?
Comment 24 Stephan van den Akker 2017-03-30 13:14:27 UTC
This should file this as a separate bug. I can not reproduce it with my own (single graph per sheet) example file.

I can reproduce with your own (4 graphs per sheet) example file on:

Versie: 5.3.1.2 
Build ID: 30m0(Build:2)
CPU Threads: 8; Versie besturingssysteem:Linux 4.4; UI Render: standaard; VCL: kde4; Layout-Engine: nieuw; 
Locale: en-GB (en_GB.UTF-8); Calc: group

Version: 5.4.0.0.alpha0+
Build ID: 17e872c3fa87bb596ec8b263b7a2e692b927dcef
CPU Threads: 8; OS Version: Linux 4.4; UI Render: default; VCL: kde4; 
Locale: en-GB (en_GB.UTF-8); Calc: CL
Comment 25 snowboard975 2017-03-30 15:21:49 UTC
(In reply to Stephan van den Akker from comment #24)
> This should file this as a separate bug. I can not reproduce it with my own
> (single graph per sheet) example file.
> 
> I can reproduce with your own (4 graphs per sheet) example file on:
> 
> Versie: 5.3.1.2 
> Build ID: 30m0(Build:2)
> CPU Threads: 8; Versie besturingssysteem:Linux 4.4; UI Render: standaard;
> VCL: kde4; Layout-Engine: nieuw; 
> Locale: en-GB (en_GB.UTF-8); Calc: group
> 
> Version: 5.4.0.0.alpha0+
> Build ID: 17e872c3fa87bb596ec8b263b7a2e692b927dcef
> CPU Threads: 8; OS Version: Linux 4.4; UI Render: default; VCL: kde4; 
> Locale: en-GB (en_GB.UTF-8); Calc: CL

Thank you for your comment.
I added the procedure of reproducing it in bug 12057 and changed its status as new.
Comment 26 QA Administrators 2018-05-11 02:32:14 UTC Comment hidden (obsolete)
Comment 27 Stephan van den Akker 2018-05-11 17:41:27 UTC
The worse behavior mentioned in comment 1 is still present in:

Version: 6.0.4.1
Build ID: 00m0(Build:1)
CPU threads: 8; OS: Linux 4.16; UI render: default; VCL: gtk3; 
Locale: nl-NL (en_GB.UTF-8); Calc: group

Master is an improvement, but the OP is still present in master from the first week of May 2018:

Version: 6.1.0.0.alpha1+
Build ID: 25c0988b87b71f93577837f4a300f5f17366d145
CPU threads: 8; OS: Linux 4.16; UI render: default; VCL: gtk3; 
Locale: en-GB (en_GB.UTF-8); Calc: CL

On a side note: Both of the tested versions make it much harder to copy multiple sheets. Right-click of one of the selected sheets now un-selects it. Copying in 6.0.4.1 and in master only works with Ctrl-drag-and-drop.
Comment 28 QA Administrators 2019-05-12 02:59:21 UTC Comment hidden (obsolete)
Comment 29 Stephan van den Akker 2020-01-10 14:24:50 UTC
Behaviour in comment 1 still present in:

LO version: 6.0.7.3 (x64)
Build ID: dc89aa7a9eabfd848af146d5086077aeed2ae4a5
CPU-threads: 4; OS: Windows 10.0; UI-render: GL; 
Locale: nl-NL (nl_NL); Calc: group

LO version: 6.3.3.2.0+
Build ID: 30(Build:2)
CPU threads: 4; OS: Linux 5.3; UI render: default; VCL: kde5; 
Locale: en-GB (en_GB.UTF-8); UI-Language: en-GB
Calc: threaded

Version: 6.5.0.0.alpha0+
Build ID: 16f8807a9255359da53e980185591e356166e414
CPU threads: 4; OS: Linux 5.3; UI render: default; VCL: kf5; 
Locale: en-GB (en_GB.UTF-8); UI-Language: en-GB
Calc: threaded