Apache OpenOffice (AOO) Bugzilla – Issue 71392
Provide unlimited conditional formatting styles
Last modified: 2016-12-05 11:06:48 UTC
Enhancement: Provide unlimited conditional formats by allowing the conditional formula to return a string naming the style to be applied. Rationale: Conditional Formatting is the only method provided by Calc to change a cell's appearance based on a value entered in the cell by the user. Conditional Formatting provides at most three alternative styles, which is not adequate for many situations. If Calc was modified simply to allow the conditional formula to return a string naming the style to be applied, an unlimited number of alternative conditional styles could be provided. Detailed explanation: (This refers to the attached spreadsheet: cond_formatting_enhancement.ods) Here are a few cells containing random values. Each cell has a style applied based on it's value: from light blue (zero) to dark blue (100). The appropriate style is applied by the cell's formula, using the STYLE() function, selecting from a table on the other sheet using a VLOOKUP(CURRENT();...) expression. This works perfectly for cells that contain formulas. What if we want to apply styles to cells that contain user-entered data? In this case the cell cannot have a formula to apply the desired style. Instead, the cell style must be applied by conditional formatting, configured through the Format > Conditional Formatting dialog. Unfortunately, the Conditional Formatting (CF) dialog limits the cells to a maximum of only three styles. There is no way to apply more than three styles, even though the application is fully capable of selecting and applying many more styles, using formulas as shown above. The current CF dialog allows the condition to be either a cell reference, or a formula. If the condition's value is TRUE, a user-selected style is applied. The enhancement requested here would allow the conditional formula to return a string naming the style to be applied. If the conditional formula returns FALSE (or an error), then the next condition is evaluated. The user interface exposing this functionality could be as simple as providing one new entry in the CF dialog's “Style” dropdown list, indicating that the style to be applied is named by the formula result.
Created attachment 40482 [details] Calc document demonstrating concepts for this issue
Hi, would a construct of IF(codition;STYLE(d1);Style(d2)) solve your problem ? Frank
I'm not 100% sure what you're suggesting, but the only way it could help (that I can see) is if you mean the STYLE function could be changed to work inside a conditional formatting formula, so that evaluating the condition would change the style of the current cell as a side effect. This does not work now--I just checked 2.0.2--but yes, that would solve the problem with no UI needed at all.
Adding another demo spreadsheet, having 10 conditional formats, generated through a basic macro: http://www.oooforum.org/forum/viewtopic.phtml?t=47065#186872 When I save it as xls it works with XL'97 until I call Format>Conditional... crash. This could be one the reason why the GUI is limited to 3 conditional formats.
Created attachment 41684 [details] 10 conditional formats
Excel has had significant enhancements in this area for Office 2007: This report claims "you are no longer limited by number; you are only limited by system memory." http://blogs.msdn.com/excel/archive/2005/10/13/480599.aspx However the product support only claims 64 conditions: http://office.microsoft.com/en-us/excel/HA100778231033.aspx#ConditionalFormatting I don't know which is correct, but either way OOo is now behind in this area.
seems to me http://www.openoffice.org/issues/show_bug.cgi?id=8812 is a duplicate
Good catch! How did I miss that? It is a request for a similar result, but through a different mechanism and interface. Issue 8812 requests unlimited conditions through extending the CF dialog to allow input of conditions beyond the current three. This proposal would provide unlimited conditions by allowing the style to be calculated by the formula in the condition. Only minimal UI changes are needed. The two enhancements would be complementary: while it might be possible to use a dialog to enter 30, 60 or 100 conditions, it would be far easier and easier to maintain to put them in a lookup table and apply them through a calculation.
> This proposal would provide unlimited conditions by allowing the style > to be calculated by the formula in the condition. However, this issue is an /exact/ duplicate of Issue 64415 Can someone please make the changes?
Another issue referring to use of VLOOKUP/STYLE with conditional formatting: Issue 73008
*** Issue 64415 has been marked as a duplicate of this issue. ***
I don't want to use macros or IF functions. This should be possible from the user-interface. Why are we limited to this arbitrary three? Novice users like myself, might not want to have to learn functions (IF functions and macros) that they wont use except for conditional formatting beyond three different conditions.
I recently noticed that it's now possible to use STYLE() directly in a conditional format formula. I don't think this has always worked, and I assume someone made the change on purpose, but it is not documented anywhere, as far as I know. E.g.: A conditional format with the formula: STYLE("Heading") will apply the "Heading" style to the cell. The value returned by STYLE() (always zero) and the "Cell style" specified in the CF dialog are apparently ignored. A CF: IF(B3<>0;STYLE("Heading");STYLE("Heading1")) applies either style based on the result of the condition. This nicely resolves the problem described here, even if in a slightly different way than requested. This issue can be closed as far as I'm concerned.
(In reply to Joe Smith from comment #13) > This issue can be closed as far as I'm concerned. Closed as asked