Conditional formatting using icon sets

[leopardhawk]

Hello forum friends, my worksheet has several cells with conditional formatting rules applied to them that will show a green checkmark, yellow exclamation mark, or a red X depending on the results of the formulas within the cells. Everything is working as it should.

However, when a user first opens the workbook, all of the data entry cells are blank and yet the cells that have the conditional formatting rules in them are still showing either a green check or a red X even though the results are all zero (I was hoping to show the results here so I captured a sample range, but it's not showing the checkmarks for some reason).

My question is to find out if there is a way to hide these green checks and red X's until there are positive results in the cells that they are associated with?

Thanks for any suggestions or ideas.

 

[jasonb75]

The icons are being applied to the formulas in the formatted cells, not the data entry cells. Any icon that you see will be based on that result, which means that the formula will need to be changed to allow for the data entry cells being empty rather than changing the conditional formatting.

 

[leopardhawk]

Ahhh, okay, thanks so much for the response.

 

[leopardhawk]

@jasonb75 I've tried a few things, can't seem to figure out how to change the CF formulas so that they don't apply an icon when the data entry cells are blank. For example, one of my rules looks thus:

Red X when value is >= (=$C$8) Formula
Yellow ! when < Formula and (=$C$8*0.95) Formula
Green checkmark when < Formula

The rule works great as long as there is data in the data entry cell, but maybe this rule isn't able to be adjusted?? If you have any suggestions, they will be appreciated and then I can apply what I learn to the other cells that have the same situation.

Thanks!

 

[jasonb75]

so that they don't apply an icon when the data entry cells are blank.

Icons will not be applied to blank cells, the formulas in the cells must be returning numeric values for them to be visible.

which means that the formula will need to be changed to allow for the data entry cells being empty rather than changing the conditional formatting.

When I said this, I meant the formula in the cell, not the one applying the conditional formatting.

 

[leopardhawk]

@jasonb75 okay, sorry, I misunderstood your earlier post. Most of the cells have very simple SUM formulas in them (i.e. =SUM(C6:C8)). If C6 and C8 are both blank cells, what can I possibly do to the target cell to prevent it from returning anything other than a zero?

 

[jasonb75]

There are 2 ways of doing that depending on what exactly is required. Note that this assumes that C6:C8 do not also contain formulas, which could create additional problems.

=IF(COUNT(C6:C8)=0,"",SUM(C6:C8)) This will SUM if 1 or more of the cells have numbers in them.

=IF(COUNT(C6:C8)<3,"",SUM(C6:C8)) This will SUM only when all 3 cells have numbers in them.

 

[leopardhawk]

@jasonb75 Thank you so much for that, it works really well. If I may ask your help one more time, I also have a number of cells with IF formulas in them and I am not sure how to apply your suggestion to those formulas. I am including one of them here in the hopes that you can help. I can then apply that same solution to the other formulae that I have. Hopefully.

=IF(I110=0,F97-(F96+F101),(F97-I110))

 

[jasonb75]

See if this does what you need, the logic is similar to the last one but there are more possible variations depending on the requirement of some or all cells being filled.

=IF(I110=0,IF(COUNT(F97,F96,F101)=3,F97-(F96+F101),""),IF(COUNT(F97,I110)=2,(F97-I110),""))

 

[leopardhawk]

@jasonb75 that's fantastic! Thank you so much. It works great on that first cell. I will use your logic to 'learn' my way through the rest of the formulas and then let you know the outcome. So far, so good! Thanks again!