Conditional formatting using nested "Or(and ..."

G

Gary

I'm using nested conditions as follows:
=OR(AND(F7="Y",K7=""),AND(H7="Y",K7=""),AND(AS7="Yes",AF7="",K7<>"X",K7<>"*"),AND(I7="$",AF7="")),
where if the conditions (AND(F7="Y",K7="") are true cell turns red, or if
the second conditions are true AND(H7="Y",K7="") the cell turns red, etc. ie,
I want each "AND" condition to be evaluated separately, so if any of the AND
conditions are true the cell will turn red. I must have my ( ) in the wrong
combinations because the formula does not produce my expected results.
 
J

John C

What results is it producing? I think you are having difficult with the
portion...
AND(AS7="Yes",AF7="",K7<>"X",K7<>"*")
And my guess is that it is K7<>"*", is it suppose to be an asterisk?
you might try changing it to K7<>CODE(42)

If this doesn't help, respond back and describe which conditions work, and
which ones don't.
 
G

Gary

Something must be wrong. Here's my reasoning using part of my formula as
follows: =OR(AND(F7="Y",K7=""),AND(H7="Y",K7=""))
If I use this part of the above formula AND(F7="Y",K7="") the cell K7 turns
red. However, if I add the second part, ie AND(H7="Y",K7="")) without
changing any values in any cells, the cell K7 does not turn red. If I enter
"Y" in cell H7, so both cells F7 and H7 = "Y", the cell K7 turns red. I
believe my formula is evaluating both of the And statements together, instead
of independently of each other. I assume there must be some way of arranging
the ( ) so each AND statement is evaluated independently so if either AND
statement is true, cell K7 will turn red.
 
D

David Biddulph

To debug your formula, copy it from your CF "Formula Is" window and paste to
a separate cell. If need be you can split it into manageable chunks in
extra cells.
Copy and paste, don't try to retype, as your problem is presumably a typing
error somewhere.
 
G

Gary

I'm not familiar with hidden characters. How would I determine if there are
any hidden characters? I have used Cell Formatting to "clear cell contents".
 
J

John C

If you cleared contents, you should be good, there are some characters that
don't necessarily display anything (such as a carriage return), and sometimes
the font is formatted to the same color of the background.

I have created the exact conditions you specify, and K7 changes to red for
each possibility, and for multiple possibilities..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top