Bug 75372 - EDITING: Error with defined names after coping sheets
Summary: EDITING: Error with defined names after coping sheets
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
QA Contact:
URL:
Whiteboard: BSA target:5.2.0
Keywords:
Depends on:
Blocks:
 
Reported: 2014-02-22 15:55 UTC by giuvi
Modified: 2016-10-25 19:08 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample file for reproduce the issue. (9.37 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-02-23 01:59 UTC, m.a.riosv
Details
MSO 2010 names dialog after copy sheet (8.73 KB, image/png)
2014-07-14 08:01 UTC, Kevin Suo
Details
Updated test spreadsheet (15.38 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-07-14 08:34 UTC, Kevin Suo
Details
screenshot of names manager which showing the problem (17.57 KB, application/x-msdownload)
2014-07-14 08:40 UTC, Kevin Suo
Details

Note You need to log in before you can comment on or make changes to this bug.
Description giuvi 2014-02-22 15:55:34 UTC
Problem description: 

Steps to reproduce:
1. Create a Spreadsheet. Any singular cells and a couple of contiguous cells (tables and table column) are defined name using Insert/Names/Define... Where as Scope the active sheet is choosed (not global).
Many cells of this sheet contain formulas using these cell names
2. Make a copy this sheet using the Move/Copy Sheet dialog and rename the new one, if you want
3. Change some reference cell values, in order to test, if the formulas containing cell names calculate correctly.


Current behavior:
1. Some of the name (not global) defined cells are not copied with the correct reference in the new sheet. The same occurs for the old one.
A look in the dialog box "Organize Names" (Insert/Names/Organize... => Ctrl+F3) can confirm the names confusion generated.

2. It seems, the formulas give the correct results. But I have not the time to check all the values. 

3. If you will to activate a certain cell, i.e. defined with name "test", and you use the Name Box, it could happens, a wrong cell will be activate, due to the behavior described on point 1.

Expected behavior:
1. The not global defined cell names should be exported in the new sheet with the cell same reference of the old one, but the sheet name reference, that must be changed in the new one.

              
Operating System: openSUSE
Version: 4.1.4.2 release
Comment 1 m.a.riosv 2014-02-23 01:59:50 UTC
Created attachment 94591 [details]
Sample file for reproduce the issue.

Hi giuvi@gmx.net, thanks for reporting.

Reproducible with:
Win7x64Ultimate
Version: 4.1.6.0.0+ Build ID: 1c33633ef18274bf384c74c492195519be83c05
Version: 4.2.1.1 Build ID: d7dbbd7842e6a58b0f521599204e827654e1fb8b
Version: 4.3.0.0.alpha0+ Build ID: d84ccb39b744457cd47125beb4291c84223d5219
TinderBox: Win-x86@39, Branch:master, Time: 2014-02-22_10:05:06

With the attached file copying the sheet:
Version 4.1.6, create the local names for the new sheet but the using range names in the new sheet don't use the values of their own sheet but the first sheet.
Comment 2 Kevin Suo 2014-07-14 07:39:36 UTC
Hi all, This is not a bug.

* In the test file, the scope for names "first" and "second" are set as Global.
* If you copy sheet1(so the duplicated sheet is named "sheet1_2"), sheet1_2.D1 and sheet1_2.D2 also refer to the global names "first" and "second" as defined in sheet1, because "first" and "second" are Global. At this point, if you change the value in sheet1_2.B1 and sheet1_2.B2, D1 and D2 will not change accordingly, because they reference to sheet1.
* For the same scope, it's impossible to give the same name two different cell ranges. (when "first" = a value in sheet1, it's impossible to let it = a value in sheet1_2 at the same time.)

* If you change the scope of "first" and "second" as "sheet1" (rather than global), then copy sheet1 as "sheet1_2", the names are copied. Now if you go to names management dialog, you see there are two "first" and two "second" names. Althrough they have the same name, they are different - they have differnt scope, so no confict during calculation.

Set as NOTABUG. Feel free to reopen if you have different opinion.
Comment 3 ign_christian 2014-07-14 07:51:40 UTC
Perhaps anyone know Excel's behavior regarding this ?
Comment 4 Kevin Suo 2014-07-14 08:01:38 UTC
Created attachment 102744 [details]
MSO 2010 names dialog after copy sheet

(In reply to comment #3)
> Perhaps anyone know Excel's behavior regarding this ?

In MSO 2010, if you copy a sheet (to the same file), when named ranges in the original is "global", then: it created a new named range with the same name, but the scope is set to the duplicated sheet.
Comment 5 giuvi 2014-07-14 08:11:36 UTC
Sorry, but I'm not agree with the current status changed to RESOLVED NOTABUG.

I didn't tested the Kevin's (Kevin Suo) test file and I completely understand, that it isn't possible to set the same defined name as global for different cell ranges - How could Calc identify the wanted cells?

In the original bug I described the case of same defined names for different cell ranges NOT AS GLOBAL, but FOR SPECIFIED SHEETS. 

Could you please reopen the bug?

Thanks a lot
Comment 6 giuvi 2014-07-14 08:14:48 UTC
(In reply to comment #5)
> Sorry, but I'm not agree with the current status changed to RESOLVED NOTABUG.
> 
> I didn't test the Kevin's (Kevin Suo) test file and I completely
> understand, that it isn't possible to set the same defined name as global
> for different cell ranges - How could Calc identify the wanted cells?
> 
> In the original bug I described the case of same defined names for different
> cell ranges NOT AS GLOBAL, but FOR SPECIFIED SHEETS. 
> 
> Could you please reopen the bug?
> 
> Thanks a lot
Comment 7 Kevin Suo 2014-07-14 08:34:52 UTC
Created attachment 102746 [details]
Updated test spreadsheet

Sorry for the noise.

I reproduce with Windows XP SP3, LibreOffice 4.3.0.2.

Steps to reproduce:
1. Open the attached file (in this comment). The file contains a two named ranges in sheet1, with the scope set as "sheet1" (rather than global).
2. Copy sheet1 and append to the same file, so the names is "sheet1_2".
3. Open "Manage Names" dialog.

Currrent behaviour:
At step 2 and 3, you can see that the names are duplicated and scope are set as sheet1_2 (-->expected), but the cell reference is still $Sheet1.$B$1 and $Sheet1.$B$2.

Expected:
Names be duplicated, and cell ranges for these names also updated ($Sheet1_2.$B$1 and $Sheet1_2.$B$2)
Comment 8 Kevin Suo 2014-07-14 08:40:02 UTC
Created attachment 102748 [details]
screenshot of names manager which showing the problem
Comment 9 ign_christian 2014-07-14 08:46:27 UTC
(In reply to comment #7)
> Created attachment 102746 [details]
> Updated test spreadsheet
This testcase better reflects the intention of this bug.

Confirmed same behavior as comment 8 with LO 4.2.5.2 - Ubuntu 12.04 x86
Comment 10 Kevin Suo 2014-07-14 08:53:24 UTC
(In reply to comment #8)
> Created attachment 102748 [details]
The "workaround(?)" is to mannually change the range to $Sheet1_2.$B$1 and $Sheet1_2.$B$2.

By the way, if you copy sheet1 to "New File", range names will be lost. This issue is discussed in bug 76523.
Comment 11 ign_christian 2014-07-14 09:21:23 UTC
(In reply to comment #10)
Bugs with copying to another file might be: Bug 61119, Bug 56518
Comment 12 royerjy 2015-03-14 21:11:48 UTC
Always the same thing with 4.4.2.1 on Windows. It is a very annoying bug when sheets have to be copied by users from a model sheet with numerous local field names.
Comment 13 Eike Rathke 2016-03-18 23:56:45 UTC
*** Bug 76523 has been marked as a duplicate of this bug. ***
Comment 14 Commit Notification 2016-03-24 16:04:47 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=ccabf4a408a246a931011732a5711e3b5334b17f

tdf#75372 rework sheet-copy names for converting global names to sheet-local

It will be available in 5.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 15 Commit Notification 2016-03-24 16:42:02 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f0cce530862e22823ae65977ea2972809237c28e

unit test for copying global names to sheet-local names, tdf#75372

It will be available in 5.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.