Bug 59391 - Naming a cell causes certain references using labeled ranges to go undefined; recalculate/autocalculate
Summary: Naming a cell causes certain references using labeled ranges to go undefined;...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86 (IA32) Windows (All)
: medium major
Assignee: Not Assigned
Depends on:
Blocks: Cell-Reference
  Show dependency treegraph
Reported: 2013-01-15 05:25 UTC by dwyoung
Modified: 2021-12-06 17:59 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Simple example of spreadsheet that fails when cell is named as documented in the problem description. (8.29 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-01-15 05:25 UTC, dwyoung

Note You need to log in before you can comment on or make changes to this bug.
Description dwyoung 2013-01-15 05:25:30 UTC
Created attachment 73046 [details]
Simple example of spreadsheet that fails when cell is named as documented in the problem description.

Naming any cell by selecting the cell and typing the name in the name box causes references to go undefined. In the example attached, the references that go undefined are in formulas in the second column that reference the column label of the third column. The failure seems to be dependent on the following conditions.
1. The "name box" is used to define the name for the cell (rather than the menu dialog "Insert > Name > Define").
2. The equations in the second column reference the label heading of the third column.
3. In the third column, the cell immediately below the column label cell contains a formula that references the label heading of the first column.

The steps for demonstrating the failure using the attached ods file are the following.
1. Open the file.
2. Select cell D5 and then enter in the name box a legal name such as 'legalName' (no quotes), thus any formula that includes 'legalName' no quotes will reference cell D5.
After naming cell D5, then cells B3 and B4 go undefined with the '#REF' error indication. Editing the formula for either cell B3 or B4 will then cause the formula to be evaluated correctly again. Recalculate doesn't fix the '#REF' errors.
I'm using, but before isolating the problem I tried 4.0.0.x and it had the same problem (though I didn't specifically try the attached simplified example with that version.)
Comment 1 m.a.riosv 2013-01-16 00:01:48 UTC
Hi dwyoung, thanks for reporting.

Reproducible with:
Win7x64 Ultimate
LibreOffice 3.3.4 OOO330m19 (Build:401)
tag libreoffice-
Versión (ID de compilación: 2ef5aff)
Version (Build ID: 527dba6f6e0cfbbc71bd6e7b88a52699bb48799)

To Enter the name through Menu/Insert/Names, do not show the error, but deleting the created name, show the error again.

Defining the labels for A1:C1 through Menu/Insert/Names/Labels before create the cell name clear the issue. But defining the labels after the cell name do not clear the issue.
Comment 2 dwyoung 2013-01-21 18:15:39 UTC
Hi mariosv, thanks for having a look and for the info.

I have only confirmed that the problem exists with Calc configuration checkbox "Automatically find column and row" asserted (Checkbox is accessed via Tools/Options/LibreOffice Calc/<checkbox> Automatically find column and row.) and the (column) labels not defined.

Subsequent to reporting the problem, I have confirmed (but not using the original attachment) that the problem can exist when a cell is named via the Insert/name/create dialog rather than only by naming the cell by selecting the cell and typing the name directly in the name box.
Comment 3 franky.chambers 2013-09-28 22:13:53 UTC
There was the same bug in ApacheOpenOffice.

See  Bug #122840 (in OpenOffice), it has been marked as VERIFIED and  FIXED.
Comment 4 QA Administrators 2015-04-19 03:21:13 UTC Comment hidden (obsolete)
Comment 5 QA Administrators 2016-09-20 09:24:38 UTC Comment hidden (obsolete)
Comment 6 QA Administrators 2019-12-03 14:08:57 UTC Comment hidden (obsolete)
Comment 7 m.a.riosv 2019-12-06 00:53:10 UTC
Version: (x64)
Build ID: a64200df03143b798afd1ec74a12ab50359878ed
CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; 
Locale: es-ES (es_ES); UI-Language: en-US
Comment 8 b. 2019-12-06 15:04:19 UTC

Version: (x64)
Build ID: 9ab43aebad67383057d2cc3f754ce2193fa78b4e
CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: GL; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US

prepare for 7'th anniversary soon ...
Comment 9 QA Administrators 2021-12-06 04:06:07 UTC Comment hidden (obsolete)
Comment 10 m.a.riosv 2021-12-06 17:59:41 UTC
Version: (x64) / LibreOffice Community
Build ID: 27d75539669ac387bb498e35313b970b7fe9c4f9
CPU threads: 4; OS: Windows 10.0 Build 21390; UI render: Skia/Raster; VCL: win
Locale: es-ES (es_ES); UI: en-US Calc: CL