use 3 icons in conditional formatting by formula

[theghost]

Hello All.

I'm trying to use an icon set via conditional formatting,
so that if certain words are selected from a drop down menu,
then certain icons are applied.
For cross posting purposes I did post here also, but no takers :-(
http://www.mrexcel.com/forum/showthread.php?t=613209

Maybe I didn't explain it to well,, I'll try my best to explain clearly now.

I have a spreadsheet.
In Column M (M5:M44) each cell has a dropdown list that says;

Please Login or Register  to view this content.

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.

If somebody can help me it really would be a great help.

I do have some formulas I've tried,, but if I post them now,,
It might confuse the above,, as some I've just guessed at,,
and I know they are way off the mark to be honest.

Actually,, what the hell,, I'll show you 2 formulas I've tried, that did at least work.
this 1st one worked to show 2 icons,(Only worked with the word completed
(This code I put in Cell P5)

Please Login or Register  to view this content.

The next 1 I found on the forums,, this works for 3 icons,, but only 3 of the words from the drop down menu.
(Again,, tried this in cell P5,, and copied all the way down

Please Login or Register  to view this content.

I really am stumped.
To used all 3 icons for all the words in the dropdown list,,?????
Is it an IF formula? With AND or OR in it??

I really really hope somebody can advise me here.
I just really don't know how.
I've tried searching the web,, & can't seem to get what I need.

So annoying,, :-)
thanks for your time.

All the best
TheGhost

[OnErrorGoto0]

It would be easier if you just use one icon set. See attached example.

[theghost]

Many many Thanks Rory!!!!!
Really really great stuff.
very much appreciated.
I would never have gotten this.

I'm kind of an ideas guy,, I like thinking about designing sheets etc,, formatting,, functionality,, basically saying "What If?" ;-)
but when it comes to code,, well,, I don't mean to swear,, but I'm crap. ;-(
I've just noticed also that Charlie45 on the other link I mentioned also gave a reply.
I'll have to get back to him in a minute also,, I really do thank everyone's help on these forums, really.
I've just tried your formula Rory, & thanks for the example sheet,, great stuff,,,it's working fine!
So,, I think I'll stick with your suggestion Rory.
The dropdown list I did use in the Name manager (Something I just about can do).

So,, it now looks like this;

Please Login or Register  to view this content.

The DropDown data is in a sheet called "DropDownData",, (Unsurprisingly!!)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I hope I've done this correctly? I mean how the code is now using the name from the name manager?
It's just that I didn't realize in the formula it would use the spreadsheet name also.

Again,, a huge thank you.
My sheet is just starting to breath.

All the best Rory
Many Thanks
TheGhost

[OnErrorGoto0]

Yes, that looks correct to me.

[theghost]

thanks for your reply Rory,,
Actually I just tried something else,,,,for the name manager,,
It's better,, and I think more correct.

It now is;;

Please Login or Register  to view this content.

Got rid of the spreadsheet name. ;-)

many thanks again Rory,,, it really is coming on now my sheet,,
I'll stick at it.

All the best
TheGhost