Created attachment 47335 [details]
Attached spreadsheet includes already defined range
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)
- 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)
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.
Added to https://bugs.freedesktop.org/show_bug.cgi?id=38565
So it won't get lost.
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.
(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 :
you still get #Name?
If you type :
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
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.
Created attachment 48700 [details]
Attached spreadsheet includes more defined range
Created attachment 48701 [details]
OOo 3.2.1 Screenshot results
Created attachment 48702 [details]
LibO 3.4.1rc3 Screenshot results
I forgot to reiterate that "Tools> Options> Calc> Calculate> Automatically find column and row labels" (check / uncheck) option should be taken into account...
> The first one (#Name is displayed) is not resolved entirely :
> If you type :
> you still get #Name?
> If you type :
> you get the right result
> This is still a regression because it is normally not necessary to type the
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
(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)...
Created attachment 48816 [details]
Libo 3.3.3 Screenshot results
I too am having a named range/database name issue with LibreOffice on Windows.
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)
all these problems will be fixed in 3.4.3