Assigning values to drop-down list options

[Guest]

I'm not even sure if Excel can do this...

I have a worksheet with 5 different drop-down lists on it (each with several
options to select from). I want to be able to assign each option a value so
that I can create a cell/formula that will average the values selected.

Can I do this in Excel? How????

 

[Pete_UK]

Your lists must be defined somewhere in the workbook - instead of a
single column of entries in each list, make it two columns where the
second column is the value that you want to allocate to that chosen
option. Then you can obtain the value by means of a simple VLOOKUP
formula (though you will have 5 of these if you have five pull-downs).

Hope this helps.

Pete

 

[Guest]

Assuming that you're using a combo box as your drop-down:

In the properties window, set BoundColumn and ColumnCount=2. Make your
ListFillRange a two-column range, with the value that will appear in the
drop-down the first column and the numeric value that you want to store in
the LinkedCell the second column. I usually use the cell in which the combo
box is located as the LinkedCell, but that's up to you. Each combo box will
store the numeric value in its linked cell, and you can then average them.

For example, your ListFillRange is the following 3X2 array:

Harpo 3
Groucho 5
Chico 2

After the user chooses Groucho, the LinkedCell will contain 5.

Good luck!

Jim

 

[Guest]

OK, so if my lists are defined within the workbook btu on a different
worksheet, that is ok? I'm assuming so since that's how I have it setup
right now for my drop-downs. I am reading how to use this "Vlookup" right
now, btu now sure how I will use it for calculating the average value of the
options selected from the drop-downs. Do I leave my drop-downs alone and use
the vlookup function onyl when creating the average formula??? Sorry, I am
too basic to be doing this on my own :-S