NOT(ISBLANK(ref)) help

[Larysa]

Hi,
I am trying to apply the conditional formatting to a column D in a spreadsheet. I need the following:
If there is a value in a cell A1-the cell D1 needs to be filled with yellow, if there is a value in a cell B1- cell D1 becomes red, if the value in cell C1=100%, D1 becomes green.
I used:
NOT(ISBLANK(A1))-for yellow
NOT(ISBLANK(B1))-for red
The value = 100% for green.
It works when I apply that conditional formatting to one cell (D1 for example). However, if i try to format several cells in a column D it highlights all of the cells in red.
What could be the problem?
Please, let me know if you have any suggestions.
Thank you.

 

[saeedkk]

Hello,

a better method to use is countA , countA would return one if A1 is filled , also countA(B1) also etc... use conditional formatting based on the 0 for no color , 1 for yellow, 2 for red, 3 for green
=IF(COUNTA(A1)=1,1,IF(COUNTA(B1)=1,2,IF(C1=100,3,0)))

hope this helps

 

[Joe4]

Note that 100% is not equal to 100, but rather is equal to 1.
So in checking column C, you need to either check for it =1 or =100%.

 

[saeedkk]

yep agree 100%
thx