Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Assigning values to drop-down list options
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???? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Assigning values to drop-down list options
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 On May 22, 12:22 am, i*sam wrote: 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???? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Assigning values to drop-down list options
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 "Pete_UK" wrote: 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 On May 22, 12:22 am, i*sam wrote: 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???? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Assigning values to drop-down list options
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 "i*sam" wrote: 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???? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop down list with different values. | Excel Worksheet Functions | |||
Mask Drop Down List values | Excel Discussion (Misc queries) | |||
How do I delete the drop down list of header / footer options in e | Excel Discussion (Misc queries) | |||
validation list with drop down list of options?? | Excel Worksheet Functions | |||
How do I set up a drop down box in Excel with a list of options f. | Excel Discussion (Misc queries) |