Bug 48856 - EDITING: NAME cell references with local scope losing reference after insertion of rows
Summary: EDITING: NAME cell references with local scope losing reference after inserti...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.2 release
Hardware: Other All
: high critical
Assignee: Markus Mohrhard
URL:
Whiteboard: BSA target:3.6.0 target:3.5.3
Keywords:
Depends on:
Blocks:
 
Reported: 2012-04-17 22:55 UTC by AxelF
Modified: 2012-04-20 07:35 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
Screen Video showing the problem (841.20 KB, video/x-ms-wmv)
2012-04-18 00:10 UTC, AxelF
Details
test file for missing formula update (7.24 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-04-19 06:47 UTC, Markus Mohrhard
Details

Note You need to log in before you can comment on or make changes to this bug.
Description AxelF 2012-04-17 22:55:19 UTC
Problem description: 

Steps to reproduce:
1. Start with empty spreadsheet file, one sheet ('Sheet1')
2. Enter any value in any cell e.g. value '110' into $B$4
3. Insert-Names-Define
4. Define Name 'field01' for range '$B$4', scope 'sheet1'
5. Enter formula '=field01' into cell C4
6. Enter formula '=$B$4' into cell D4
7. Cells C4 and D4 display value of cell B4
8. Insert new row above row 4
9. Moved cell C5 displays '0' (lost reference to 'field01') = bug
10.Moved cell D5 displays value of cell B5 = correct
11.Delete row above row 4
12.All values are displayed correctly

Current behavior:
NAME references without absolute sheet address are not handled correctly inside the sheet. If the 'range' (see step 4) is defined 'sheet1.$B$4' instead of '$B$4', then the NAME reference is correctly moved by inserting rows or lines into the sheet. But then the NAME reference (scope) is no longer limited to sheet1 only, but is a GLOBAL scope, which makes duplicating of sheets impossible.

Expected behavior:
Local scoped NAME references must be handled similar to non-named cell references.

Platform: Win7
              
Browser: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:11.0) Gecko/20100101 Firefox/11.0
Comment 1 AxelF 2012-04-18 00:10:50 UTC
Created attachment 60238 [details]
Screen Video showing the problem
Comment 2 Peter Schmitt 2012-04-18 01:53:44 UTC
I can confirm this for Windows XP SP3 as well.

The Reconfigure-mechanism of defined cell references (i.e. moving automatically fixed cell references depending on row/col-delete/insert-actions) should be consistent for direct cell references (e.g. $C$8) or NAMED ones as shown in "Screen Video".

This is at the moment a great migration obstacle from Excel to LibO because of the danger of losing all used NAMED references in table functions only by inserting/deleting rows or columns.
Comment 3 Markus Mohrhard 2012-04-18 07:39:17 UTC
This is a bug for me. Looks like I have missed one case for range name formula updating.
Comment 4 Markus Mohrhard 2012-04-19 06:47:50 UTC
Created attachment 60311 [details]
test file for missing formula update

Test file contains a local range name referencing $C$8 and a formula =test in E8.

Inserting a row before row 8  will now result in a correct range name update but the formula seems to be recalculated.
Comment 5 Not Assigned 2012-04-19 06:53:31 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

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

update relative local range names, fdo#48856
Comment 6 Markus Mohrhard 2012-04-19 06:55:54 UTC
Fixed the range name update problem. Still a little problem with the update of the formulas.
Comment 7 Not Assigned 2012-04-19 14:30:39 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

fdo#48856 update sheet-local named expressions correctly
Comment 8 Not Assigned 2012-04-19 15:38:01 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-3-5":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9a9121929408e8ffc86b91606355ff5fe9514f7d&g=libreoffice-3-5

resolved fdo#48856 update sheet-local named expressions correctly


It will be available in LibreOffice 3.5.4.
Comment 9 Not Assigned 2012-04-20 07:35:41 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-3-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=51648779ccf76bc7c6b6ff1ed4cd32eb75af9a5a&g=libreoffice-3-5-3

resolved fdo#48856 update sheet-local named expressions correctly


It will be available already in LibreOffice 3.5.3.