Conditional Formatting issue...

[Eloise T]

I wish to flag the following three words if they show up in Column F:
REBILLED, REPAID, PAID
Creating three Conditional Formats works (see below):
=$F3="REBILLED"
=$F3="REPAID"
=$F3="PAID"

How can they be concatenated into one Conditional Format?

The following don't work...
=$F3="REBILLED" OR "REPAID" OR "PAID"
=$F3="PAID,REBILLED,REPAID"
=$F3="PAID".OR."REBILLED".OR."REPAID"
=$F3="REBILLED" & "REPAID" & "PAID"

 

[John Jairo V]

Hi @Eloise T!

You can use:
=OR($F3="REBILLED",$F3="REPAID",$F3="PAID")

Or:
=($F3="REBILLED")+($F3="REPAID")+($F3="PAID")

Blessings!

 

[Eloise T]

Hi @Eloise T!

You can use:
=OR($F3="REBILLED",$F3="REPAID",$F3="PAID")

Or:
=($F3="REBILLED")+($F3="REPAID")+($F3="PAID")

Blessings!

Thank you!

 

[SirJB7]

Hi, Eloise T!
At the edition or input bar, either if you press the "f(x) " at the left or if you input "=<function>(" (unquoted) you're led to the function built-in help, which is classified by function type. There you can read the related help, and as you type the formula, you're prompted for each parameter. This is a good way to construct formulas, it would have aided you with your F3 intents. You knew that you have to use OR function, well, type "=OR(" and see what happens.
Regards!

 

[NARAYANK991]

Hi ,

When the words will be used as they are , and not when they are stored in cells , then another possibility is as follows :

Define a named range , calling it say CATEGORIES , and in the Refers to box , enter ={"REBILLED","REPAID","PAID"}

Your CF formula will now be :

=OR($F3 = CATEGORIES)

Narayan

 

[Eloise T]

Hi ,

When the words will be used as they are , and not when they are stored in cells , then another possibility is as follows :

Define a named range , calling it say CATEGORIES , and in the Refers to box , enter ={"REBILLED","REPAID","PAID"}

Your CF formula will now be :

=OR($F3 = CATEGORIES)

Narayan

This is whole new concept for me.


In Cell E1, only REBILLED shows up? ={"REBILLED","REPAID","PAID"}

When I put one of the three in, the opposite happens from what I expected.
Cell E1 lights up rather than Column F cells. Interesting....

 

[NARAYANK991]

Hi ,

A named range is defined using the Name Manager.

Narayan

 

[Eloise T]

Hi, Eloise T!
At the edition or input bar, either if you press the "f(x) " at the left or if you input "=<function>(" (unquoted) you're led to the function built-in help, which is classified by function type. There you can read the related help, and as you type the formula, you're prompted for each parameter. This is a good way to construct formulas, it would have aided you with your F3 intents. You knew that you have to use OR function, well, type "=OR(" and see what happens.
Regards!

I will try that. Thanks.

Hi ,

A named range is defined using the Name Manager.

Narayan

More for me to digest and look into.
Thank you.

 

[Frank M]

Enjoyed reviewing the info in this thread, and also appreciate all the help I have received in the past from this website.

Using Narayan's (a tremendous Excel resource - Thanks) coding example of
=OR($F3 = CATEGORIES) I wanted an EXACT match (case sensitive) so I expanded the code as follows:

=OR(EXACT($F3 = CATEGORIES) )

Not sure if you needed this capability but it does work.

 

[bobhc]

For some one who says she does not use social media outlets you sure know them all, pleased to say I have never heard of most of them........

 

[Eloise T]

For some one who says she does not use social media outlets you sure know them all, pleased to say I have never heard of most of them........

I cheated and Googled them, otherwise, like you, I've never heard of them.
...a few were invented on the fly....

 

[Eloise T]

Enjoyed reviewing the info in this thread, and also appreciate all the help I have received in the past from this website.

Using Narayan's (a tremendous Excel resource - Thanks) coding example of
=OR($F3 = CATEGORIES) I wanted an EXACT match (case sensitive) so I expanded the code as follows:

=OR(EXACT($F3 = CATEGORIES) )

Not sure if you needed this capability but it does work.

Thank you! Good to know!