Icon Set Conditional Format using Index+Indirect Not Working

[Punnam]

Hi Friends,

I Am trying to Automate the Icon Set Conditional format

Having names in Column "A" , i have percentage in Column B,C & D , i need to color in column B,C & D to be changed based on Name in Column " Level " (A),

When ever i change the value they are showing randomly.

Find the Attached sample Sheet

Regards,
Punnam

[Richard Buttrey]

Would you list the green amber and red values for each staff level in a SIMPLE table. i..e a table containing 18 values, 3 each for 6 levels.

[Punnam]

HI Richard ,

I have updated the attachment in my 1 Post , Kindly check .

For example Partner

0.35 & 0.25 is if the cell value is Les than 0.25 : Red , if Cell Value >.25 & <.35 : Yellow , if Cell Value is >0.35 Green.


Punnam

[Punnam]

Hi ,

Looking for Help on Conditional formatting Icon Set.

Regards,
Punnam

[kvsrinivasamurthy]

This can be done by running macro with code below.
If the values in range I6:N7 is changed macro should be run once again.
Macro will do CF from the values in I6:N7 at the time of running.
CODE:

Please Login or Register  to view this content.

[Punnam]

Hi kvsrinivasamurthy,

Thanks for macros unfortunately i cannot use it in my company file, any help with Formula will be great full.

Regards,
Punnam

[kvsrinivasamurthy]

Are worksheet events are allowed?

[Punnam]

I am not sure but will try them if you help me

Punnam

[samba_ravi]

I think you need to change the type to formula form Number in Conditional Format

Conditional format > Edit Rule > Type here Change Number to Formula

[kvsrinivasamurthy]

Worksheet event is also coded in VBA . So is it allowed by company or not please Confirm.

[kvsrinivasamurthy]

Inserting additional cells Icon CF with formula shown in File.
CF for B6
In B6 Formula

=IF(C6="","","n")

Then change the font of B6 to Webdings.

Now a black dot is seen in B6.

Formulas for CF of B6

For RED
=C6< INDEX(Paercentage,2,MATCH($A6,Group,0))

For Yellow
=AND(C6> =INDEX(Paercentage,2,MATCH($A6,Group,0)),C6< =INDEX(Paercentage,1,MATCH($A6,Group,0)))

For Green
=C6> INDEX(Paercentage,1,MATCH($A6,Group,0)).

Now copy B6
Paste to cells in B , D and F columns.

For details

https://www.youtube.com/watch?v=yEODN8sMHVY