Human error in conditional formatting formula...

[appreciatethehelp]

Mojave 10.14, LIbreoffice 6.2

Hi guys. So admittedly I’m still not flash with the formulas used in Conditional Formatting… I am trying to make a certain cell turn a certain colour when a phrase is present in another group of cells.

I had asked a similar question here: Conditional formatting- change cell's background colour when ANOTHER cell equal to, however I still can’t seem to figure out where I’m going wrong in the attached document:

Conditonal formatting formula error example.ods

Could someone please correct me, and provide a brief explanation as to what I’ve done wrong?
I will take the time to read the relevant chapters on Conditional Formatting in the Guide, but for now I need to work out how to do this quickly for an upcoming deadline on a project.

As always, cheers.

[mikekaganski]

You need to make several corrections.

1. You need to prepare your sample carefully. E.g., pay attention to the ranges you mention in the explanations: looks like you need to check range A4:A26, but both explanations an existing conditional formatting mention A2:A24.

2. You need to use formula like

   COUNTIF($A$4:$A$26;“yes”)>0

because conditional formatting uses usual rules for evaluation of references, including rules for relative vs absolute references, and rules for using a cell from a rang found by intersection. Specifically, you used

$A2:A24="Yes"

in the range A1:A3. That meant that you used usual formula with equality check (simple =, not an array formula), with relative references (well, you used $ before column name, but only in one case; and not before row numbers), which translates to “find (by intersection) a cell in range one cell below till 25 cells below; check if that single cell is equal to “Yes”.”

Note that COUNTIF uses case-insensitive check, so that using “Yes”, “YES”, or “yEs” will all do.

And finally, you would need to define the range for your conditional formatting to be A1;A3- not A1:A3 (note using semicolon instead of colon) - because you need to exclude A2 from the conditional format.