frequency of formats

[flipinoluv]

I have an excel work sheet that uses colors in a matrix of people and skills they posses.

along the y axis would be the people, along the x axis would be the skill.

i am denoting colors, green=skilled, blue=needs refreshment, red=not trained.

I need to tabulate how many skills each person has.

Is there a way to be able to count the number of times a color, (i.e green) box occurs in a row/column?

 

[xgerryx]

Welcome to TSG. No joking you are TSGs 100,000th member.

 

[WhitPhil]

This page and this one show how to do it using a VBA function.

 

[flipinoluv]

Thanks

 

[flipinoluv]

I am trying to add drop down menus so that the user can only choose from a basic set of colors so that there can be no mistake in counting by color by choosing another shade.

I found i was unable to create a drop down box that only included colors.

I found out a way around this using conditional formatting, substituting text and just making the text the same as the background color....

However, for some reason, the COUNTBYCOLOR will not count or change its value. For some reason, conditional formatting does not change the 'official' pattern style of the cell and therefore it wont register it as a change in color.

2 questions...

1) Is there a way around this?

2) How can I add more conditional formats?

 

[XL Guru]

>> drop down menus so that the user
>> can only choose from a basic set of colors

?

>> unable to create a drop down box that only included colors

???

If you'd like a color picker, try here.

http://j-walk.com/ss/excel/tips/tip49.htm

Rgds,
Andy