Ask Your Question
0

Human error in conditional formatting formula...

asked 2019-08-21 05:55:45 +0200

appreciatethehelp gravatar image

updated 2019-08-21 06:02:19 +0200

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: https://ask.libreoffice.org/en/questi..., however I still can't seem to figure out where I'm going wrong in the attached document:

C:\fakepath\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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-08-21 08:43:41 +0200

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-08-21 05:55:45 +0200

Seen: 32 times

Last updated: Aug 21