Suggestion: Calc should allow different styles, applied to the same range, in conditional formatting. The way it works nowadays is awful. How it should work: 1. It should allow setting up the order the conditional formats are applied. 2. If a conditional format affects DIFFERENT cell attributes: both conditional formats should be applied. Ex.: CF1 changes only cells background and CF2 changes only font color. If a cell content matches both CF1 and CF2, it will have a background ant color font changed. 3. If a conditional format affects the SAME cell attribute, I imagine 2 possibilities, to be thought and chosen: 3.1. Only the first CF is applied. 3.2. Both of them are applied, so the last one remains. 3.3. Another possibility is to warn the user and ask her/him to choose the desired behavior.
(In reply to syllaslp from comment #0) > Suggestion: Calc should allow different styles, applied to the same range, > in conditional formatting. > > The way it works nowadays is awful. > > How it should work: > > 1. It should allow setting up the order the conditional formats are applied. Editing the conditional format it's possible to move up/down the inner conditions > > 2. If a conditional format affects DIFFERENT cell attributes: both > conditional formats should be applied. Ex.: CF1 changes only cells > background and CF2 changes only font color. If a cell content matches both > CF1 and CF2, it will have a background ant color font changed. Maybe amazing, but I think we are not going to view such enhancement in a short time if implemented at some time, because I think not so easy to code, generate an internal style mixing those, and perhaps slowing the spreadsheet. In any case as the styles has inheritance, you can make a tree of styles and apply the one that meets the conditions. > > 3. If a conditional format affects the SAME cell attribute, I imagine 2 > possibilities, to be thought and chosen: > > 3.1. Only the first CF is applied. > > 3.2. Both of them are applied, so the last one remains. > > 3.3. Another possibility is to warn the user and ask her/him to choose the > desired behavior.
Sounds like over-engineering to me. Way too many if-then that no one but the developer can understand. => WF
I think ideas 2 and 3 will make it all too complicated, on top of the choices already available. And 1 works. So bot WF en WFM apply, AFAIAC :)
We discussed this topic in the design meeting and agree with Cor's comment 3. Simplicity is trump and very specialized solutions are better realized via macro. Thanks for reporting anyway. We definitely appreciate input from users.
Sounds like the 3 members of the UX team discussing this issue missed a major point. Suggestion 1 is not some hypothetical, dream feature. It 1 is precisely how Excel has worked for decades and it supported by both XSL and XLSX formats. Users coming from other spreadsheet software expect to be able to order the conditional formats (not just inner conditions). Since the original report was overly broad, I propose we limit this report to just the first suggestion: adding the capability to re-order outer conditions. The current XLSX importer reverses the order of outer conditional formats. If you save as ODF, there is no way to fix this issue without recreating the conditions. Allowing re-ordering would improve interoperability and is the behavior that users expect. See attachment 114686 [details] for an example of how this feature would be useful for fixing import errors along with giving LO users a feature that Excel has had for years.
Hi Luke, (In reply to Luke from comment #5) > Sounds like the 3 members of the UX team discussing this issue missed a Uhh.. > major point. Suggestion 1 is not some hypothetical, dream feature. It 1 is > precisely how Excel has worked for decades and it supported by both XSL and > XLSX formats. Could you please give your view on this comment then? (m.a.riosv from comment #1) > (In reply to syllaslp from comment #0) > > 1. It should allow setting up the order the conditional formats are applied. > Editing the conditional format it's possible to move up/down the inner > conditions Thanks,
Created attachment 152467 [details] Simple Example to Highlight UI Problem (In reply to Cor Nouws from comment #6) > Could you please give your view on this comment then? I've been working with conditional formatting since Excel 97. It's very common to want to change the order of outer conditions. Without this functionality, making changes is painfully slow and error prone. In Excel it's a simple clicking “move up” or “move down”. In Calc, you have to first delete the top condition, then add it again below. This is a very common scenario for anyone working with conditional formatting. For example you might have data that you want organized one way for local markets then you'd want to present it a different way for international markets. I've seen this many times. To see an example of how problematic our current UI is, take the attached file now try to make the 2nd rule evaluate first. In Excel, it's as simple as Conditional Formatting -> Rule 2 -> Move up(twice) Now try this in Calc, while retaining both rules just like Excel. The lack of move up / move down commands makes our conditional formatting very cumbersome to work with.
Let me tell you what I've desired: a) To get alternated background lines, like in Excel tables; b) To change font colors depending on their values and types, but only in a specific numeric column: b.1. Default, for negative; b.2. Blue, for positive; b.3. Brown, for text (preceded by an apostrophe). I've got to create 6 conditional format rules, a combination of them (2 x 3): 1. Default font color + default background 2. Default font color + darker background 3. Blue font color + default background 4. Blue font color + darker background 5. Brown font color + default background 6. Brown font color + darker background Otherwise, result wouldn't be good, but it isn't reasonable! If you want to apply different formats to different columns and do the same to lines, the number of CF rules and complexity will increase geometrically!
syllaslp, Short answer: a) Long answer: It's just a coincidence that my example highlights a bug in our import filter with the order of overlapping conditions. Your feature request 1) would make fixing this class of bug easy. Even if that xlsx import bug is fixed, we are still left with the UI issue of not being able to reorder outer conditional format. If users do not plan their conditions out perfectly, they will be stuck in a position where the only fix is to delete them and restart. I think this is unacceptable.
Created attachment 152702 [details] Mockup for the proposed change So let's do it. Buttons labelled up/down might be more accessible but are less obvious and my proposal is to have up/down buttons right hand of the controls in both dialogs.
(In reply to Luke from comment #7) >... > Now try this in Calc, while retaining both rules just like Excel. The lack > of move up / move down commands makes our conditional formatting very > cumbersome to work with. Thanks for the example. Now I understand that the option to move conditions is only available in one place. So +1 one for (at least the left part of) Heiko's mock-up.
*** Bug 127510 has been marked as a duplicate of this bug. ***
*** Bug 105325 has been marked as a duplicate of this bug. ***
I support Luke's comment #7 (https://bugs.documentfoundation.org/show_bug.cgi?id=126047#c7).