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

 

[RoryA]

You told me you entered 1/2 and it got converted to a date. I assume you corrected that, but I don't know how. Try using the OP's original formula.

 

[Stormseed]

OK, I guess I would need to explain from scratch.

I enter the value "1/2" (without quotes) in cell A1 (cell A1 is conditionally formatted with the above formula). The moment I enter, the conditional format fails (it means the cell does not display the selected conditional format pattern) and the value is converted into a DATE.

Besides, in all due respect, I do not expect that you must post a response. Leave it & let go incase you feel I am bugging you with my probing of questions.

Thanks for your help though Atleast you made me realize there is something wrong in Excel on my end !

 

[RoryA]

The OP's formula returns "1/2" as text, hence the conditional formatting works and the COUNTIF should work. If you formatted your cell as Text and then entered 1/2 manually, that should work too (for both).

 

[Stormseed]

So ? Rory, I told exactly the same thing above. Now, that the formula returns 1/2 as text just because the IF() condition posted in some other thread incorporated by the OP is in a text format ("1/2"). The cell should be formatted as "TEXT" manually or through a formula and it is doing through a formula at the moment in order for the Conditional Format to work.

It was my mistake that I did not convert the format of that cell on my end to "TEXT" the first time I tested it on my end - I realize this and the second time I format the cell as "Text" and it finally worked - Pheeww

Thanks for your understanding and now I am out of confusion. Rory, you were the one who confused the heck out of me on this petty problem, I wont forget that

 

[RoryA]

The cell is not formatted by the formula.
The point of this thread was to solve the OP's problem, which we have done; the only confusion arose out of your insistence that the cell had to be formatted as Text, which it did not. Hence, I cannot truly take the credit for your confusion. I stand by my original PICNIC comment.

 

[Stormseed]

The cell is not formatted by the formula

But the formula returns a text value, right ?

 

[RoryA]

Yes (but that does not change the cell format)

 

[Stormseed]

Yeah, exactly.

I had no formula plonked into my worksheet as the OP did hence there was no possibility that any formula would return a value in the cell where I am testing the conditional format at my end. I had to manually enter the value 1/2 which inturn got converted into a Date. The second time I formatted the cell (just because the problem at my end of the value getting converted) as text and it worked well.

I expect you to realize this and take back your silly comment. It was not funny

 

[RoryA]

I realize all of that (as I already said) so my comment stands - there is nothing wrong with your Excel.

 

[Stormseed]