Created attachment 72140 [details] my document which has this problem Problem description: -------------------- I have 2 style for conditional formatting, one have green background, other have red background. When cell content is 'y', then the color is green, otherwise the color is red Steps to reproduce: ------------------- 1. block the cells 2. use format - conditional formatting 3a. condition1 : cell value is same with 'y', apply style -> create new style with green background. 3b. condition2 : cell value is not same with 'y', apply style -> create new style with red background. Current behavior: ----------------- some cell has green background, and other is red. whether the value is y or not (it doesn't care). When the cell contains y is clicked, the style with green background is highlighted (format and style window). When empty or other than y is clicked, the style with red background is highlighted. Expected behavior: ------------------ After executing command, all cells contains y will have green background. All cells that does not contains y will have red background. Operating System: Windows 7 Version: 3.6.4.3 release
I can confirm that conditional formatting behaves incorrectly in the attached file in Version 4.0.0.0.beta2 (Build ID: 4104d660979c57e1160b5135634f732918460a0) on Ubuntu 12.04 (x86) Recalculating doesn't change the behavior in my test
Now I know the behavior of conditional formatting. It only works on single cell. Also the character is enclosed with double quote. I update the steps : 1. You must select 1 cell only. 2. Apply conditional formatting. 2a. condition1 : cell value is same with "y", apply style -> create new style with green background. 2b. condition2 : cell value is not same with "y", apply style -> create new style with red background. 3. Then copy it using format-brush to the other cells you want. The result is just as expected, all cells is green if contains "y". And red if not.
Markus Mohrhard committed a patch related to this issue. It has been pushed to "libreoffice-4-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=2eebc768c4d48f384f14142516df7d8b3a751097&h=libreoffice-4-0 don't add a new cond format if we just want to edit one, related fdo#58781 It will be available in LibreOffice 4.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Markus Mohrhard committed a patch related to this issue. It has been pushed to "libreoffice-4-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=3ea8026aefc358483292b459aaaead830e1f667d&h=libreoffice-4-0 handle strings correctly in ScCondFormatEntry::GetExpression, fdo#58781 It will be available in LibreOffice 4.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Markus Mohrhard committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=faa7667d4a249d1a497610a10c64ac54799c08f4 don't add a new cond format if we just want to edit one, related fdo#58781 The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Markus Mohrhard committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d6fd3ccb105e164fdc826f1cafff2f5323194794 handle strings correctly in ScCondFormatEntry::GetExpression, fdo#58781 The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
@markus: do you think this can be added to 3.6 code too? Thanks,
(In reply to comment #7) > @markus: do you think this can be added to 3.6 code too? Thanks, I have to check the 3.6 code before that. However the document contains some errors in itself like the use of ='Y' instead of ="Y" which is the correct way to use strings in formulas.
(In reply to comment #8) > However the document contains some errors in itself like the use of ='Y' > instead of ="Y" which is the correct way to use strings in formulas. the 'Y' I got from automatically from conditional formatting dialog. While "Y" I got from reading the manual. I think the dialog itself must give double quote if user give string (or character) as argument.
(In reply to comment #9) > (In reply to comment #8) > > > However the document contains some errors in itself like the use of ='Y' > > instead of ="Y" which is the correct way to use strings in formulas. > > the 'Y' I got from automatically from conditional formatting dialog. While > "Y" I got from reading the manual. I think the dialog itself must give > double quote if user give string (or character) as argument. No! 'Y' is also a valid formula and therefore valid input. It just means something different. There is no way to differentiate between 'Y' and "Y" for the formula compiler and you as user have to use the correct form.
(In reply to comment #7) > @markus: do you think this can be added to 3.6 code too? Thanks, The bug fix is only for 4.0. In 3.6 the problem was not existing like that. There the only problem was the use of 'Y' vs "Y"
(In reply to comment #11) > (In reply to comment #7) > > @markus: do you think this can be added to 3.6 code too? Thanks, > > The bug fix is only for 4.0. In 3.6 the problem was not existing like that. > There the only problem was the use of 'Y' vs "Y" Hmm, I checked it before I asked, of course. And got the same wierd results while using double quotes... But now cannot reproduce it. So I prolly did something wrong there. Sorry.
(In reply to comment #10) > No! 'Y' is also a valid formula and therefore valid input. It just means > something different. There is no way to differentiate between 'Y' and "Y" > for the formula compiler and you as user have to use the correct form. Then when one chooses e.g "equal to" and just enters Y, without any quote, it is converted to 'Y'. Is that what a user should expect? Do you have any simple hint on difference 'Y' and "Y" (looked for it via Google, but apparently not hard enough..)
> > Then when one chooses e.g "equal to" and just enters Y, without any quote, > it is converted to 'Y'. > Is that what a user should expect? That sounds strange. Y => range name/formula function, 'Y' => column label, "Y" => string. However there might be some nasty quirks in the formula parser that notices that this file contains column labels with the name 'Y' and therefore when it can't find Y as function and range name uses it as column label. > > Do you have any simple hint on difference 'Y' and "Y" (looked for it via > Google, but apparently not hard enough..) See above. It is not obvious but it is part of OpenFormula.
(In reply to comment #12) > > > Hmm, I checked it before I asked, of course. And got the same wierd results > while using double quotes... > But now cannot reproduce it. So I prolly did something wrong there. Sorry. When I read the manual, it says to select a cell. It means literally "a" cell, not many cell. So I select just one cell, apply cond format, then copy that cell format to other cell using "format paintbrush", the one in the right of "paste" button.