Share via

Excel - Conditional Coding - Problem with Icon colors working properly

Anonymous
2018-04-04T20:33:42+00:00

Hello,

I'm having a funny issue with my conditional coding on one of my projects.

I have two columns, one is for input (with set validation of Red, Green or Yellow as choices) - see below.

As I type Green, "state" is updated with a green circle, yellow with the triangle, etc.

I have the following formula in state column:  =VLOOKUP(K5,{"red",1;"yellow",2;"green",3},2,0)

If you look illustration #1 below, all looks good, but if I change the 3rd item "Red" to "green" then the yellow icons become red...see illustration #2 below.  Any idea why?  Does it have to do with how i set up the conditional coding?  Maybe i have the wrong set up in my conditional coding?  See illustration #3 showing that.  

Thanks for any help!

ILLUSTRATION #1

ILLUSTRATION #2

ILLUSTRATION #3

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2018-04-04T20:48:32+00:00

That specific conditional formatting relies on population statistics - the 1, 2, and 3 values in the column L.  But if you don't have a Red in K, then the lowest value becomes 2, which means all the 2 values are in the lowest third of values.

Change your formula in L5 to  

=VLOOKUP(K5,{"red",-1;"yellow",0;"green",1},2,0)

and copy down, then change your CF as in the attached picture:

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-04-04T21:11:09+00:00

    Thanks so much!!! this resolved the problem.  Bless you!

    Was this answer helpful?

    0 comments