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
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.
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.
Perhaps anyone know Excel's behavior regarding this ?
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.
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
(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
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)
Created attachment 102748 [details] screenshot of names manager which showing the problem
(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
(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.
(In reply to comment #10) Bugs with copying to another file might be: Bug 61119, Bug 56518
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.
*** Bug 76523 has been marked as a duplicate of this bug. ***
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.
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.