Bug 52565 - (regression) Conditional Format formula for wrong cell is displayed
Summary: (regression) Conditional Format formula for wrong cell is displayed
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.6.0.2 rc
Hardware: Other Linux (All)
: medium normal
Assignee: Markus Mohrhard
URL:
Whiteboard: target:3.7.0 target:3.6.1
Keywords:
: 52300 (view as bug list)
Depends on:
Blocks:
 
Reported: 2012-07-26 22:24 UTC by Jim Avera
Modified: 2012-08-25 12:08 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
Demo spreadsheet with conditional formatting (7.82 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2012-07-26 22:24 UTC, Jim Avera
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jim Avera 2012-07-26 22:24:05 UTC
Created attachment 64757 [details]
Demo spreadsheet with conditional formatting

The Conditional Formatting dialog sometimes shows information about
the wrong cell (that is, some cell other than the current selection).

Please open the attached CondFmt.ods spreadsheet, which was created
with LibreOffice 3.4.4

Click any cell in column A, then Format->Conditional Formatting
and display Condition #1.  Every cell shows the Formula

    MOD(B1,2)=0

which is incorrect except for cell A1.  In all the others, the formula is

    MOD(Bx,2)=0     [where x is the row number]

That this is the case can be confirmed by observing that the 'Yellow' style is conditionally applied only when the number in column B is even.  

You can also open the spreadsheet using LO 3.4.4 and see the correct formulas.

Marking this as critical because IMO this regression must be fixed before 3.6 is released, as it makes it impossible to rationally edit Conditional Formats.
Comment 1 Markus Mohrhard 2012-07-27 20:30:59 UTC
This behavior is technically not as wrong as you think and will for sure not be fixed for 3.6.0

So now to the technical explanation: A conditional format range has always a base cell and only one formula is saved against the base cell. Currently the dialog just uses the base cell to regenerate the formula from the internal representation and not the selected cell. The original mock-up from Astron contained a solution for this problem but I'll was not able to implement all features in time. I think the solution until 3-7 is that we use the cursor position to calculate the formula.

Please also note that the formula is correct as you can see from the applied formatting.
Comment 2 Markus Mohrhard 2012-07-27 20:31:19 UTC
Oh, and this is definitely not a critical bug.
Comment 3 Markus Mohrhard 2012-07-27 21:05:27 UTC
*** Bug 52300 has been marked as a duplicate of this bug. ***
Comment 4 Not Assigned 2012-07-27 21:10:01 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=06b5dfff3aa3005093d427e368ae5eeb5f2ad948

use correct cursor pos for cond format formulas, fdo#52565
Comment 5 pierre-yves samyn 2012-07-28 07:51:36 UTC
Hello Markus

However, I suspect a hidden problem. The formula is not always correct.

I can not find a rule but it is reproducible:

1. open the attached CondFmt.ods spreadsheet
2. Select C1:C10
3. Format> Conditional formatting> Conditionnal formatting> Add
Type: "Formula is", Formula:  MOD(B1; 2)=0, Apply style "Yellow"
4. Ok

Expected result: yellow background for the even lines only
Actual result: all cells of the range in yellow

Note : actual result ok if we use this formula: ISODD(ROW())

5. Select D1:D10
6. Format> Conditional formatting> Conditionnal formatting> Add
Type: "Formula is", Formula: B1=2, Apply style "Yellow"

Expected result: D2 in yellow because B2=2
Actual result: no cell in the range applies the style "yellow", even if you type 2 in B1

Platform: Windows 7 64bits & Version 3.6.0.2 (Build ID: 815c576)
Reproduce with Version 3.6.1.0+ (Build ID: c3cd975)

Regards
Pierre-Yves
Comment 6 Markus Mohrhard 2012-07-28 10:05:14 UTC
(In reply to comment #5)
> Hello Markus
> 
> However, I suspect a hidden problem. The formula is not always correct.
> 
> I can not find a rule but it is reproducible:
> 
> 1. open the attached CondFmt.ods spreadsheet
> 2. Select C1:C10
> 3. Format> Conditional formatting> Conditionnal formatting> Add
> Type: "Formula is", Formula:  MOD(B1; 2)=0, Apply style "Yellow"
> 4. Ok
> 
> Expected result: yellow background for the even lines only
> Actual result: all cells of the range in yellow
> 
> Note : actual result ok if we use this formula: ISODD(ROW())
> 
> 5. Select D1:D10
> 6. Format> Conditional formatting> Conditionnal formatting> Add
> Type: "Formula is", Formula: B1=2, Apply style "Yellow"
> 
> Expected result: D2 in yellow because B2=2
> Actual result: no cell in the range applies the style "yellow", even if you
> type 2 in B1
> 

That is the same problem. We always used A1 as base cell for new entries which made the formulas point to different cells.
Comment 7 pierre-yves samyn 2012-07-28 11:04:42 UTC
Ok, same problem but different effect, and more critical: Formatting not apply.. don't you think ?
Comment 8 Markus Mohrhard 2012-07-28 12:50:53 UTC
(In reply to comment #7)
> Ok, same problem but different effect, and more critical: Formatting not
> apply.. don't you think ?

I don't know what you mean with htat. Both problems are fixed with my commit and will be fixed in 3.6.1 There is no chance to get it into 3.6.0 because that is already tagged, builds are already produced and will be released next week.
Comment 9 Not Assigned 2012-07-30 20:17:28 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "libreoffice-3-6":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=77792fef0f7d199dab5c3bc065410968c7e6680b&g=libreoffice-3-6

use correct cursor pos for cond format formulas, fdo#52565


It will be available in LibreOffice 3.6.1.
Comment 10 pierre-yves samyn 2012-08-25 12:08:17 UTC
Hello

Works for me with Version 3.6.1.2 (Build ID: e29a214) & Windows 7 64bits

Regards
Pierre-Yves