Conditional formatting to include text?

[Detectiveclem]

Hi again,

I was wondering if any one can help me with the following.

I am currently using conditional formatting to change the colour of a cell based on data contained within another cell using the following formulas


=($X3<=31) this turns cell B3 fill to red,
=($X3<=62) this turns cell B3 fill to orange,
=($X3<=93) this turns cell B3 fill to yellow,

but want the formatted cell to also include text, i.e. the red to display "Urgent", orange to display "Soon" and yellow "OK".

Can anyone tell me what formula I need to use for this to work?

Thanks all help will be much appreciated,

 

[Special-K99]

This is setting the value in B3 not the format.
Try

=LOOKUP(X3,{31,62,93},{"Urgent","Soon","OK"})

 

[Detectiveclem]

Thank you Special-K99,

This doesn't do exactly what I am trying to achieve, is there a way to include a formula into my formula above, i.e. when the cell goes red it will also include the text "urgent", orange "soon" etc.

Thanks,

Paul

 

[Special-K99]

You're asking for two different things.
To change the FORMAT of B3
and to change the VALUE of the B3.

You're going to need two formulas.

To change the format use the Conditional Format as you've correctly specified.
To change the value use the LOOKUP formula above.

 

[adam087]

You could edit the Number Format on the relevant conditional format to include the text...

Example, if your existing data format is number to no DP, then set a custom number format of
<code>"Urgent" #,##0; "Urgent" -#,##0</code>

Does that help?

/AJ

 

[Detectiveclem]

Thank you Special-K99,
I now understand it and have managed to get it to work as I want.
However after copying this formula down the rest of column B when the corresponding cell still awaits to be populated the following is displayed #N/A, can you tell me how to get the formula to leave these cells blank until i populate the corresponding cell?

I hope I have explained this properly.

I really appreciate your help.

Regards,
Paul

 

[Detectiveclem]

Hi Adam, thank you but I am still very inexperienced in many aspects of Excel and not quite sure how to add the formula you suggest to my formula as shown in my original post above. Perhaps if you could explain step by step so I can try it and also understand how it works. Also what does 'DP' mean in your post?

Thanks,

Paul

 

[adam087]

DP in that sense meant Decimal Places. Apologies for the jargon.

Basically, you can see how this works by going to one of your cells, right clicking and choosing 'Format Cells' then choosing the Number tab.
You've probably been here before and recognise most of the options like 'Number' and 'Currency'.
Well if you go to the 'Custom' option you have a lot more flexibility to change the real nuts and bolts of how the number format works... including entering in plain text with an example like mine above.
Have a play.

Once you've got some options you're happy with, then you can think about adding it to the Conditional Format.
You've already set up the Conditional Format to change the colours, so go ahead and edit that, and click through to change the format as if you were going to set a different colour.
But instead of changing the colour you'll see the same 'Number' tab along the top which you can go to and change the number format to include text, for when the condition is met.

Does that help?

/AJ

 

[Detectiveclem]

Hi Adam, this makes more sense now. I look forward to playing around with it. Thank you for getting back to me.

Best wishes,

Paul

 

[adam087]

No problem. Thanks for the feedback.

/AJ