google-sheetsconditional-formattingcomparison-operators

How to use a Cell Reference to a Comparison Operator within Conditional Formatting in Google Sheets

In Google Sheets, can you do a cell reference, to a cell with a comparison operator ( >, <, >=, <=, <> ) in it, within conditional formatting?

For instance, you want to test =A1>A3, but rather than typing the > directly into the conditional formatting formula, you have a > in cell A2. This way the user can see what comparison operator is being used for conditional formatting, and change it if needed.

So the conditional formatting formula would be something like =A1 & A2 & A3 or maybe =A1&"A2"&A3. But these do not work.

Here is my attempt:

conditional formatting formula

Do you know how to accomplish this? Thank you!

Solution

  • Formula building

    =IF(COUNTIF(A1,A2&A3),TRUE)

    enter image description here

    Note

    COUNTIF return 1 or 0

    Use IF to return TRUE or FALSE

    Conditional formatting (just use COUNTIF)

    =COUNTIF($S$20,$X$24&$Y$24)

    enter image description here

    Function References