Can I suppress an Icon in one column based on the value of another?

[Mike Wyller]

I'm trying to use Icon Sets to indicate the 'Age' of the last update made to the record, but only want to display this 'Aging' icon when the Status (in another column) does NOT contain certain text values (i.e. 'Ready', or 'Resolved', or 'Moved').

I have a range of cells (NoShow) containing status text values that if they appear in my data, DO NOT display any icon. I can test my data for this 'exception test' a couple of ways:
=SUMPRODUCT(--ISNUMBER(SEARCH(NoShow,$2)))>0
(another way to test for values is "=FIND("Moved",$B2)")

I just don't know how to combine these tests to get the result I want.
I've attached a sample...

 

[Chihiro]

You need second set of Conditional format.

First, modify your formula in C2 to...
=IF(ISNUMBER(MATCH(B2,NoShow,0)),"",NETWORKDAYS.INTL(A2,TODAY(),1))

Then add new CF with formula:
=$C2=0
Edit: Should be ""

And put it to top of CF list and Check off "Stop if True".


Edit2: Go with Luke's Method

 

[Luke M]

Welcome to the forum!

Looks like a simple change to the formula in col C to not give a number when not needed. In C2, put:

=IF(OR(B2={"Ready","Resolved","Moved"}),"",NETWORKDAYS.INTL(A2,TODAY(),1))

Then copy down as needed.

 

[Mike Wyller]

Oh Man Luke - that's perfect, thanks.
Not to sound greedy, but can I search B2 for "Ready","Resolved","Moved" text anywhere in a string - they way this works? =ISNUMBER(MATCH(B2,"Ready",0)

 

[John Jairo V]

Hi, to all!

You can use:
=IF(OR(ISNUMBER(SEARCH({"Ready","Resolved","Moved"},B2))),"",NETWORKDAYS.INTL(A2,TODAY(),1))

or

=IF(OR(COUNTIF(B2,"*"&{"Ready","Resolved","Moved"}&"*")),"",NETWORKDAYS.INTL(A2,TODAY(),1))

Blessings!

 

[Luke M]

Mike, glad you liked it. Looks like John beat me to the answer. Cheers!

 

[Mike Wyller]

Thanks very much John & Luke.
Now I'll spend some time learning exactly how these versions work so when the time comes, I can help others out just like you did. Can't thank you enough.