Excluding 0 from Conditional Formatting

[bearcub]

I have the following formula:

=INDEX(client_CTA_co_organization_regi!$S$2:$S30000,MATCH($B2&$C2,client_CTA_co_organization_regi!$B$2:$B30000&client_CTA_co_organization_regi!$C$2:$C30000,0))

It looks compares an email field between the client_CTA sheet and the email field in column J.


I have a conditional formatting rule that says if the Col J and S on the client_CTA sheet don't match highlight the cell red.

The rule works fine except in some instance both the email field and the client_CTA document are empty (neither have an email address in them).

In those cases where both cells are blank I get a zero value and the cell is highlighted red. I would like to exclude this value from the rule. I've tried using Len, IsBlank, W2 = 0 (column W is where I have the formula), W2 = 0. Nothing seems to work. Am I doing something wrong and is there a formula that I could use to create another rule to exclude 0 from the rule?

Thank you for your help.

Michael

 

[bearcub]

I just tried W2 = 0 in another column and got true for those that contained zero. when I put this formula in custom formatting it didn't work. I'm just scratching my head trying to figure out what I'm doing wrong. Any suggestions?

Thank you for your help,

Michael

 

[jtakw]

Hi,

If you Don't want the cell highlighted when the value is 0, then you need the cell to return FALSE, not TRUE, so =W2<>0

 

[bearcub]

thank you for that tips (I hadn't tried that one yet). But, that doesn't work either. I already have 2 other rules in the column to high errors and to highlight blank email cells in column J (but there is an address on the client sheet).

The W2=0 does work on empty cells (formulas extended beyond the data). When I put in your argument cells that have something them are impacted. So, I'm still at a loss.

I think the issue is the result returned by the formula. I know it is a zero because I've tried W2 = 0

Val      Result     Formula
0	  TRUE      =W6 = 0

Thank you again for your help

 

[jtakw]

If you have Other CF rules, it's possible that one rule is taking precedence over others.
If you post the CF rule formulas you're using, and explain how and when something should be highlighted, and how and when it shouldn't, may be we can help you figure it out.

 

[bearcub]

I was thinking along those lines as well so I put this one for the zeros at top:

This is the one that isn't working (the member is on both reports but is missing the email address on both):
1) W2 = 0

The next rule I have this one that looks for errors in the cell (if the source document is returning an error - it can't find the members name on the source document so no email address is returned):

2) Iserror(W2) - this returns a yellowish red fill

The last rule I have is where the emails between column J and the Client sheet don't match

3) J2<>W2

could it be possible that I have to adjust one of the 2 for this one to work.

 

[jtakw]

So for your First rule, have you tried =W2<>0

 

[bearcub]

yes, All the cells will contain something; Either text, an #N/A error, or a 0.

Going from the top to bottom, I have the W2 = 0 firing first, then the IsError firing second, then the W2<>J2 firing third.

For some reason W2<>0 isn't working because I do have zero values in those cells I want to exclude.

Extremely frustrating (that no matter what I try nothing seems to work. I can't seem to find something on the web that deals with this directly - or I'm not asking it correctly)

 

[jtakw]

Wait a minute, shouldn't it be =AND(J2=0,W2=0) if you want the cell highlighted when both are 0 , or
=AND(J2<>0,WE<>0) if you want the cell NOT highlighted when both are 0 ?

 

[bearcub]

I don't want zero to be highlighted at all, it is highlighted in the formula J2<>W2.

If Column J is empty but an email exists on the client sheet I get the email address highlighted in red.

If Column J is empty and the member record on the client sheet doesn't have an email address I get 0.

I would like the 0 (which means both the report and the client sheet are both missing an email address) to be highlighted a different color.

This will let us know that our main database doesn't contain an email address for the member.