a conditional format tweak for icons

Just to add,,
Do I ned the formula in Cell P5?????????
Which is,

Can it be achieved by just using formulas in the icon sets & reference M5 (Drop down words)??
See attached


Not sure what the formula would be

I came across another forum post here;
http://www.mrexcel.com/forum/showthread.php?t=589125

This now gives me 3 of the icons to use.
code is;
Thing is,,
1.. If the drop downs don't equal any of the above 3,, I get #N/A showing in my cells
2.. Anyway to teak the above formula so that with "In progress" I can add;

"Just Started"
"On Hold"

???????
I did just try and guess,,, failed miserably
Just an Idea,,,might be somewhere near?????????

Many Thanks
JC

Just tried this; Excel accepted the formula,, but it didn't work
Must be near?????????

Any ideas anybody please...
many Thanks
JC

I love excel!! (When it works,, or shall I say, I get it to work)

Sorry,,, I'm wrong Totally!!!!!!!!!!!!!!
it didn't accept it,,, I just didn't have the = sign in the formula bar!!!!!!!!!!!


I need a coffee.

I still can't get this!!! ......
maybe I haven't explained it to well.
Let me try again,,, with no fluff.
=============================
I have a spreadsheet.
In Column M (M5:M44) each cell has a dropdown list that says;
In Column P (P5:P44) I would like each cell to display1 of 3 different icons
via conditional formatting,, depending on what word has been selected
from the drop down list mentioned above. If M cell is Blank (No Icon)

3 conditions I'd like would be:

ICON Set (Green Tick) If "COMPLETED" is selected.
ICON Set (Goldish Triangle) If any of "Not Started" or "Just Started"
or "In Progress" or "On Hold" are selected
ICON Set (Red Cross) If "Cancelled" Is Selected


That's it.
I just don't know how to write the formula for the corresponding P Cells,
Or in the conditional formatting,, if these need formulas,, what they would be.
(Maybe they don't need a formula in the conditional formatting? Depends i suppose on the formula used in the P cells?

If somebody can help me it really would be a great help.
It's really really stumped me.

I just need the 3 icons applied to the 6 items mentioned in the dropdown list.
Help Please!!!

many thanks again
JC

In P5 your formula would be =IF(M5="Cancelled",3,IF(M5="Completed",4,2))

Your Conditional formating would be > 3 for the Green check
Your Conditional formating would be > 2 for the Red X
Your Conditional formating would be <=2 for the Yellow triangle


What did you want if M5 were blank?

Many Thanks again for your reply Charlie45,,

I really was struggling with this.
I did recently post on another forum for an answer,, here,, (for cross posting purposes. here's the link..
http://www.excelforum.com/excel-general/814595-use-3-icons-in-conditional-formatting-by-formula.html

A guy called Rory replied.
(OnErrorGoto0)
I saw this reply before I saw that you have replied here Charlie45
I tried his example sheet,, it seems to all work fine,,
he done it slightly differently though.

This was in his example sheet,, with the F range showing the dropdown words I'd want to use.
My formula now is;
I'm not 100% sure if I inserted the name from the name manager correctly,,,,,,,,,,,,,
###AS I'M TYPING THIS I JUST TRIED SOMTHING,,IT WORKED,,,,
Formula now is;
Ok,, great stuff.
This seems to work fine,, & leaves blanks, IE no icon showing if a blank is selected.

I'll probably stick with Rory's code Charlie45.
Again,, many thanks for your help,, really appreciate it.
I'm sure your IF formula,, I can use elsewhere soon... I can now see how you combine 2 IF's together.

All the best,, and thanks again,,

A very grateful
John Caines

glad you got you problem worked out. Thanks for the feedback.