Conditional formatting

[jackdiamond]

Hello,

I have this formula in a cell =IF('IAN COLLINS'!J$22<=0,LOOKUP('IAN COLLINS'!J$6,{0,0.1,0.51},{"","1/2","1"}),"S")

When the cell shows an "S", can I do a conditional format to show the cell red?

thanks for any help
Stu

 

[jackdiamond]

that all works fine. Thank you all very much for your help.

just one more thing......

If I have formulas along cells like previosly mentioned and the show for example 1 , 1/2 and S in the cells, what formula could i use to add up the totals of all the S's or all the 1's for example?

Some kind of look up function?

thanks again

 

[Stormseed]

In CF, Cell Value Is, equal to, I entered:

="1/2"

formatted the cell and clicked OK. It did not work on Excel 2003 unless I formatted the cell with conditional format - as "Text". I will try it again when I go home on Excel 2007 and see if it works.

 

[RoryA]

I'm using 2003. How did you put the 1/2 in the cell?

 

[Stormseed]

that all works fine. Thank you all very much for your help.

just one more thing......

If I have formulas along cells like previosly mentioned and the show for example 1 , 1/2 and S in the cells, what formula could i use to add up the totals of all the S's or all the 1's for example?

Some kind of look up function?

thanks again

Does that work without formatting the cell as "TEXT" ? Please clarify.

 

[jackdiamond]

yes, without formatting the cell. (assuming you are still talking about CF not my next question?)

Cheers
Stu

 

[Stormseed]

I'm using 2003. How did you put the 1/2 in the cell?

Laugh Out Loud

Assuming the cell A1 is the one which is conditionally formatted, I entered exactly: 1/2 and pressed enter immediately (I am on a windows XP desktop with SP3 & I am rite now in office, at work). The value entered was converted to a DATE by default and thereby causing the conditional format rule to fail.

 

[RoryA]

You can use Countif:
=COUNTIF($A$1:$A$100,"1/2")
for example.

 

[Stormseed]

You can use Countif:
=COUNTIF($A$1:$A$100,"1/2")
for example.

Rory, but could you please tell me what is wrong at my end ?
It works on OP and your PC though

 

[RoryA]

I don't know what you entered, so it's hard to say. I assume it's a PICNIC error...

 

[Stormseed]

Rory
I told you exactly what I entered and you are in a mischievous mood now