Conditional Format with ICON set based on text

**nadiminti** · 12-29-2014, 02:37 AM

Dear Team,

I have the following problem of conditional format based on text value. Many suggestions were to have an additional column and put a numeric #. As the data I have is very huge, require help.

In each of the cell of the columns, the data I have shall be either:

Planned, WIP, Completed. (user selectable from a drop-down list by using Data Validation)

If it is Planned, I want the ICON set to be Red, for WIP -- Orange and for Completed -- Green.

Please let me know

Best Regards
Sriram

**udprocks** · 12-29-2014, 02:57 AM

Originally Posted by nadiminti

Dear Team,

I have the following problem of conditional format based on text value. Many suggestions were to have an additional column and put a numeric #. As the data I have is very huge, require help.

In each of the cell of the columns, the data I have shall be either:

Planned, WIP, Completed. (user selectable from a drop-down list by using Data Validation)

If it is Planned, I want the ICON set to be Red, for WIP -- Orange and for Completed -- Green.

Please let me know

Best Regards
Sriram

Hi Sriram,

Looking for like this?

**nadiminti** · 12-29-2014, 03:01 AM

Hi Jitendra,

Thank you. Not exactly. I need the ICONS - like Traffic Lights to show up. The coloring can be done by simple Conditional Formatting.

Thanks

**Vikas_Gautam** · 12-29-2014, 03:31 AM

Check the attached file and tell is that what you were trying to achieve..

**samba_ravi** · 12-29-2014, 03:48 AM

small modification to Vikas Gowtham

**teylyn** · 12-29-2014, 03:50 AM

@udprocks, please don't quote whole posts. Don't hit the "Reply with Quote" button. Scroll to the bottom and type in the Quick Reply box instead.

**teylyn** · 12-29-2014, 04:07 AM

udprocks, Vikas, nflsales,

Why don't you describe your solutions in the text of the answer? Why do you force us to open your files to see what you have done? And even then, it may not be immediately obvious to the OP exactly WHAT it is that you have done and how to get there.

Please don't just post files and throw them at people. A good answer explains the steps in WORDS in a POST right here. A file can be used as an illustration, but not as the sole solution.

Imagine someone searching for a solution and using Google. If you describe your approach in WORDS, Google will index your words. If you just post a file, Google goes right past it and your brilliant solution will not be found by anyone who is not already in this forum.

nadiminti, yes you need a helper column. You cannot have an icon set on a text column. A formula in the helper column can evaluate the text and return a number based on the text. You can then use conditional formatting with an icon set on the helper column.

The screenshot shows a data layout that has the text in column B and the helper column in column C. The formula to translate the text value in column B to the value required for the Icon set is:

=LOOKUP(B2,{"completed","planned","wip"},{3,1,2})

The conditional formatting is set to hide the number in the cell and show only the icon. Using formatting of the cells, the two columns can appear as one.

2014-12-29_21-08-22.png

cheers, teylyn