Request the ability to apply the format returned by a formula. This is in essence like the STYLE() function, except applied via conditional formatting rather than by explicit use of STYLE().
Use case: applying a style to manually entered data. For example, a formula that returns a named style based on the year of an entered date (perhaps by means of LOOKUP()) would have the style applied by conditional formatting to the entered cell.
The date method of conditional formatting is not appropriate for this case; the basic conditional formatting could be used, but only up to 10 years (or other ranges). It's also less flexible, because the formula to be used for the computed conditional format could actually be a name; editing the value of the name changes the formula, which would allow the computed style to be changed easily everywhere.
Hi RLK -
So sorry for the long delay - I think in this case it's largely due to a lack of understanding about the use case for this enhancement request. Would you be so kind as to explain a bit clearer what exactly you're looking to accomplish. I vaguely understand the use case but not enough to mark this as NEW. I'm going to mark this as NEEDINFO - if you could just explain (in really simple terms....speak as if the QA member has no idea what you're referring to...like STYLE() function...no idea what that is).
Once you explain a bit more mark as UNCONFIRMED and we'll try to at least confirm this.
Again, so sorry for the delay moving this forward.
Lastly - as you probably know, this is an open source project so even if it's confirmed as a valid request, it might or might not be implemented (depends on a volunteer taking interest).
A style is a collection of cell settings, including font, background color, border, etc. Styles are each named; the F11 key opens the style editor, where you can create, modify, and apply styles. So I could for example define a style named "Emphasis" with a bold, red font and 2 point border.
The STYLE() function can be used in formulas to apply a style to a cell. So I could have a formula
that sets the cell to the value of cell A1 and applies the "Emphasis" style to it. The argument to the STYLE function can be computed, just like any other. One idiom I use a lot is
where CURRENT() is the function returning the current value of the computation, Ranges is an array of values, and Styles is an array containing a list of style names. The expression can be even more complex than that.
That all works fine for a cell containing a formula. If the cell contains a value that the user enters, this doesn't work. The only way to do variable formatting in a value cell is with conditional formatting.
Conditional formatting is done via a list of conditions and styles. There can be up to 10 conditional styles in the list. A condition can be either a simple test or a formula, but the formula returns either true or false. The style corresponding to the first true condition is applied. There are a few other ways of doing conditional formatting, such as color scales, but those don't work for what I want to do.
What I want to do is have a new kind of conditional formatting where the "condition" is a formula that returns the name of a style, in the same manner as examples 1 and 2. Fixed conditions are too unwieldly for this purpose; it's limited to 10 expressions, and if I want to add in another slot, I have to redo all of the conditions.
If I understand correctly what you want to do (English is not my mother tongue), you can use the STYLE function in the conditional formatting functionality :
Choose the option "Formula is" and use STYLE(LOOKUP(A1,Ranges,Styles))
(In reply to GerardF from comment #3)
> If I understand correctly what you want to do (English is not my mother
> tongue), you can use the STYLE function in the conditional formatting
> functionality :
> Choose the option "Formula is" and use STYLE(LOOKUP(A1,Ranges,Styles))
rlk: is this enough for you?
Set to NEEDINFO.
Change back to UNCONFIRMED, if it is not enough. Change to RESOLVED INVALID, if it does what you want.
If that works, I believe that would do what I want.
Confirmed that it works as desired. Not that intuitive, but then again, the STYLE function isn't that intuitive to begin with.
There's still a problem, though; at least in some situations, use of this technique results LibreOffice (both 4.2.7 and 5.0.2) using close to 100% of a CPU even if everything's computed and it's waiting for input. It doesn't freeze the interface, although there does appear to be a slowdown, but it shouldn't be doing that. I wasn't able to reproduce it with a small, simple case, but would you prefer I use this request or open a new one for that?
(In reply to rlk from comment #7)
> There's still a problem, though; at least in some situations, use of this
> technique results LibreOffice (both 4.2.7 and 5.0.2) using close to 100% of
> a CPU even if everything's computed and it's waiting for input. It doesn't
> freeze the interface, although there does appear to be a slowdown, but it
> shouldn't be doing that. I wasn't able to reproduce it with a small, simple
> case, but would you prefer I use this request or open a new one for that?
Please open a new report. Thanks!
Comment added to bugt 95233:
What I usually do for cells with formulas is
=a1+b2+STYLE(LOOKUP(CURRENT(), FooRanges, FooStyles)
where FooRanges and FooStyles might be adjacent columns that look something like this (this is an actual example -- number of meters I've rowed in a session, to be precise):
I'd like to be able to do this for text entry cells, but easily change the breakpoints (which are used elsewhere) without having to change both the lookup table and the conditional formatting (which won't even work in this case due to the number of ranges).
Reopening 85264 would be one way of handling this.