A question about excel, drop-down boxes, thank you?

Question

I consider myself comfortable with Excel, however there is something that i am unsure of how to do and would appreciate your help in finding a solution.

I created a spreadsheet, basically a self evaluation. in my table, i would like one of the columns to contain a drop-down box in each cell, where three options present themselves (ie X, Y Z)

When i selection X the cell turns blue
When i selection Y the cell turns red
When i selection Z the cell turns yellow

I'm then looking to count the number of options selected

eg

x = 2
y = 4
z = 4

as opposed to having three seperate colums in my table, at the moment my three columns = x y z

to count the option i simply put a 1 where appicable, and then count the sum. the solution is the same result, but for data entry purposes its not as clear as could be.

I'm thinking this is involoves some VB,

if you can point me in the right direction, that would be excellent.

thankyou

Answer 1

You could do this with VB, but you don't have to. Set up conditional formats (under the "formats" menu) for the colors. Make the condition if cell is = X then format (color), if cell = Y then format (color) etc. You can define 3 different conditional formats for each cell. Then in a cell of your choosing, just do a =count(cell range) to get your totals. I hope I explained this clearly enough. Play with the conditional formatting and see how that works to get your colors. The "total options" part is easy.

The drop down box would have to be a VB statement set, but is not really necessary for your stated requirement unless you are just trying to get fancy.

Answer 2

First of all make a list in say column Z --- In Cell Z1 enter X, In Cell Z2 enter Y, in cell Z3 enter Z

Now suppose you want to make the drop down boxes in column C, select the entire column by clicking on the column top "C".

From Data menu select validation and allow "List". In the Source type in Z1:Z3

This way you will have a drop down box on each cell in Column C, which will allow on "X", "Y" or "Z" to be input.

Then from the format menu select "Conditional Formatting" and add colors as you wish.

To use conditional totals use the function "Sumif"

Need more detailed help - contact me

Answer 3

decide on the selection, then call it say MyList you're able to try this by applying 2 techniques one million. decide on the selection then decide on the call field beside the formulation field enter a one observe call MyList 2. decide on the selection then visit the stunning menu and decide insert,call,define interior the 1st field enter the call MyList decide on the cellular that demands the drop down checklist then visit the stunning menu and decide archives validation decide on checklist from the enable field then interior the source field sort this =MyList then ok

Answer 4

[e-mail]....emial me a copy I might be able to better understand your final goal.