Conditional Formatting not always Applied [closed]

asked 2017-02-09 12:30:35 +0200

Joe0815 gravatar image

updated 2020-08-09 11:47:37 +0200

Alex Kemp gravatar image

Hello there! I think I might have found a bug here.

I use conditional formatting on a column with calculated numbers. A neighboring column (right side) contains a hard defined set of minimum values. This column is masked (not visible).

If the value of a cell is negative, a style with red color should be used. If the value is positive, but below its minimum value, a style with yellow colour should be used. If none of the above conditions apply, a szyle with blue colour is to be used.

My problem is, that, often (not always), if I load the file, negative values are not displayed in red. If, for one line, I change some other values, so that the respective cell turns positive, and redo this by redoing the other values, the style is again displayed correctly, even though all values are the exact same as in the first place.

I found one method to completely restore the correct display without touching the numbers: - Step 1: Insert a new empty column anywhere in the document - Step 2: Remove the new column again via the "Undo" functionality. (Manually removing it does not work) And everything is displayed correctly again.

Recalculating the cells via "F9" also does not work, by the way.

This formula (German) is used: =F9+M9-SUMME(P9:EU9)+VORLAGE(WENN(AKTUELL()<30;WENN(AKTUELL()<0;"Red";"Yellow");"Blue"))

(Edited by @Lupp:)
Please use English function names in the English forum. The specific example should read then:
=F9+M9-SUM(P9:EU9)+STYLE(IF(CURRENT()<30;IF(CURRENT()<0;"Red";"Yellow");"Blue"))

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-08-09 11:47:54.513740

Comments

Usage of the STYLE function is completely different in its working from conditional formatting.
Did you actually talk of formatting based on STYLE from the beginning?
("Applying named cell styles depending on nested IF function calls")

Lupp gravatar imageLupp ( 2017-02-09 14:03:42 +0200 )edit

OK, then I used the wrong term. Probably, it would have been easier then to use conditional formatting right from the beginning...? However, I used the STYLE function, as mentioned in my formula, nothing else. Feel free to rename my question.

Joe0815 gravatar imageJoe0815 ( 2017-02-09 14:20:42 +0200 )edit

Pls test if hard recalc [Ctrl+Shift+F9] works

m.a.riosv gravatar imagem.a.riosv ( 2017-02-09 23:32:26 +0200 )edit

Yes, hard recalc works. Some more info: After I did my work-around yesterday, I saved the file and today, the error does not re-occur. For testing the hard recalc, I had to retrieve the old file version from our weekly backup. So, this should definitely be something, that is saved with the file.

Joe0815 gravatar imageJoe0815 ( 2017-02-10 10:53:38 +0200 )edit