Bug 37763 - Define a range name prevents recognition of a label
Summary: Define a range name prevents recognition of a label
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.4.0 release
Hardware: x86 (IA32) Windows (All)
: medium major
Assignee: Not Assigned
URL:
Whiteboard: target:3.5 target:3.4.3
Keywords: regression
Depends on:
Blocks:
 
Reported: 2011-05-30 12:21 UTC by pierre-yves samyn
Modified: 2011-12-23 14:16 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
Attached spreadsheet includes already defined range (7.53 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-05-30 12:21 UTC, pierre-yves samyn
Details
Attached spreadsheet includes more defined range (11.19 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-07-02 23:55 UTC, pierre-yves samyn
Details
OOo 3.2.1 Screenshot results (6.15 KB, image/png)
2011-07-02 23:56 UTC, pierre-yves samyn
Details
LibO 3.4.1rc3 Screenshot results (14.94 KB, image/png)
2011-07-02 23:56 UTC, pierre-yves samyn
Details
Libo 3.3.3 Screenshot results (9.46 KB, image/png)
2011-07-06 05:57 UTC, pierre-yves samyn
Details

Note You need to log in before you can comment on or make changes to this bug.
Description pierre-yves samyn 2011-05-30 12:21:37 UTC
Created attachment 47335 [details]
Attached spreadsheet includes already defined range

Hello

Build Date & Platform: XP LibO3.4RC2 buildid=340m1(Build:12) 

Steps to Reproduce:

- Create a new spreadsheet (reproduced on an existing one)
- In E5 enter Europe
- In E6 100
- In E7 200
- Select E5:E7
- Data> Define Range (Name: Europe, Range: $Sheet1.$E$5:$E$7), 
  Leave the option "contains labels" checked (default) > OK
- In A1 type = COUNTA(Europe)
- Twice OK (confirm the name and formula)

Expected Result: 3 (regardless of "contains columns labels" is checked because the defined range is not used, it is the label that is searched)

Actual result: formula =COUNTA(#NAME?) displays : #NAME?

This is a regression (worked in OOo 3.2.1 and LibO 3.3.2)

Additional Information: 
- Tested and reproduced on Vista [fr-discuss]
- Same problem whatever the state of the checkbox "Tools> Options> Calc> 
Calculate> Automatically find column and row labels" (check / uncheck)


Best regards
Comment 1 Rainer Bielefeld Retired 2011-06-10 02:57:44 UTC
RC2 is bit by bit identical with release version, so separate items in the version picker are useless. Changes have been discussed with Michael Meeks.
Comment 2 Markus Mohrhard 2011-06-23 19:38:09 UTC
Added to https://bugs.freedesktop.org/show_bug.cgi?id=38565

So it won't get lost.
Comment 3 Markus Mohrhard 2011-07-02 10:13:54 UTC
Ok, I think we have two issues here. the problem that #Name is displayed should be fixed in 3.4.1 already. Can you try that?

But I get with a build from the 3.4 branch only 2 as result. The problem seems to be in ScCompiler because our ScToken only represents the range without the label.
Comment 4 pierre-yves samyn 2011-07-02 23:53:46 UTC
Hello 

(In reply to comment #3)
> Ok, I think we have two issues here. the problem that #Name is displayed should
> be fixed in 3.4.1 already. Can you try that?

Tested with XP LibO 3.4.1rc3 buildid=340m1(Build:103) (same as the finale release I guess...)

I think you're right. We have maybe two bugs depending on what the software supposed to do in this case : use the defined data range, or search the range from his column label.

IMHO there are two bugs...

The first one (#Name is displayed) is not resolved entirely :

If you type :
=counta(Europe)
you still get #Name?

If you type :
=counta('Europe')
you get the right result

This is still a regression because it is normally not necessary to type the quotes


> But I get with a build from the 3.4 branch only 2 as result. The problem seems
> to be in ScCompiler because our ScToken only represents the range without the
> label.

I completed the attached spreadsheet to handle different cases: defined ranges, including or not the column labels. I added the screenshots of the results in OOo 3.2.1 and 3.4.1rc3 Libo

My reasoning is as follows.

The column for Asia works as expected: result (2) correct. No data defined range and the result is calculated after searching for the column label that should not be counted.

The result is the same as you enter or not the quotes around the column name.

Having two different behaviors for the Europe column, depending on you type or not the quotes, suggests that the software uses the defined range if we do not type the quotes.

The OOo screenshot shows (C7:C8) that the formula preserves the exact entry (quotes or not) with different results

If this is correct (if the data range defined is used) the software should take into account the parameter "contains the data labels" from the defined data range. This is not the case. 

The result 3 in OOo is incorrect because this setting is checked.

For Africa column  where a range is defined with the parameter unchecked the result should be 3 and it is 2.



Best regards
Pierre-Yves
Comment 5 pierre-yves samyn 2011-07-02 23:55:09 UTC
Created attachment 48700 [details]
Attached spreadsheet includes more defined range
Comment 6 pierre-yves samyn 2011-07-02 23:56:19 UTC
Created attachment 48701 [details]
OOo 3.2.1 Screenshot results
Comment 7 pierre-yves samyn 2011-07-02 23:56:55 UTC
Created attachment 48702 [details]
LibO 3.4.1rc3 Screenshot results
Comment 8 pierre-yves samyn 2011-07-03 00:03:19 UTC
I forgot to reiterate that "Tools> Options> Calc> Calculate> Automatically find column and row labels" (check / uncheck) option should be taken into account...
Comment 9 Markus Mohrhard 2011-07-05 21:35:53 UTC
> The first one (#Name is displayed) is not resolved entirely :
> 
> If you type :
> =counta(Europe)
> you still get #Name?
> 
> If you type :
> =counta('Europe')
> you get the right result
> 
> This is still a regression because it is normally not necessary to type the
> quotes
> 

I don't think so, you're using a database range and not a named range. So I don't expect that Europe would work. But I'm not quite sure
Comment 10 pierre-yves samyn 2011-07-06 05:57:08 UTC
(In reply to comment #9)

> I don't think so, you're using a database range and not a named range. So I
> don't expect that Europe would work. But I'm not quite sure


Well, It worked like this in previous versions, including version LibO 3.3.3 330m19(Build:301)...
Comment 11 pierre-yves samyn 2011-07-06 05:57:42 UTC
Created attachment 48816 [details]
Libo 3.3.3 Screenshot results
Comment 12 kurt forrester 2011-07-18 06:43:17 UTC
I too am having a named range/database name issue with LibreOffice on Windows.

LibreOffice 3.4.1 
OOO340m1 (Build:103)

I have an Ubuntu box at home (LibreOffice from PPA which I think is 3.3.2) where the spreadsheet was developed and the spreadsheet worked fine. I then opened the file at work on windows machine and the name of the range in the formulas is replaced with #NAME? (that is I type in the cell =SUM(data_capex) and the formula is replaced with =SUM(#NAME?) after I press enter)
Comment 13 Markus Mohrhard 2011-07-19 11:29:17 UTC
all these problems will be fixed in 3.4.3

http://cgit.freedesktop.org/libreoffice/calc/commit/?h=libreoffice-3-4&id=d688aee70f87acbfe273b814d21b5dba343fc9d9