Excel Combo Box

[MichaelSS]

Does anyone know how to get a combo box in excel to show data and put a number elsewhere?

Right now I have a combo box showing high, medium and low. When the user select one I would like to put a number elsewhere. I know how to place it in the actual cell but when high, mediumk or low is selected it puts a 1,2 or 3 in the cell.

Can anyone help???

 

[01Patrik]

The combobox is returning a cellcount inside the range of your combobox - meaning that if it returns a 2, it is the second value of the combobox range.

There are several ways of getting the actual value in your cell (if I understand your questions correctly?):

1/ Use HLOOKUP, same range as the combobox, and the combobox cell reference as rowindex

2/ Use validation instead of combobox. Tools | Validation - select list, then "=yourrange" (easiest is to assign a name to the range - then the range can be anywhere in your workbook, otherwise the range must be placed on the same sheet as the validation)

The second option gives you the choise right away in the cell with the validation, whilst the combobox needs two extra cells to achieve the same.


// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[MichaelSS]

I may not have explained it incorrectly:

The combo box needs top sho the choices hot, medium & low.

The actual value of .01(high), .02(medium) & .03(low) needs to be placed elsewhere.

 

[Zathras]

There are at least three ways to have a combo box with High,Medium,Low on a worksheet:

1. Combo Box from the Forms toolbar.
2. Combo Box from the Control Toolbox toolbar.
3. Automatic drop-down when using Data Validation.

You didn't specify, but I infer from the fact that your results are numbers, you are using method 1.

I assume then that you have set an "Input range" and a "Cell link". In that case, to get your actual value you could either use a formula that divides the value in the "Cell link" cell by 100 or use the =INDEX function to retrieve the corresponding value from a parallel range corresponding to the "Input range". (The divide by 100 is not recommended because the values will no doubt eventually be updated to be some other non-linear function of the High/Medium/Low designations.)

For example:
[tt]
E3: High
E4: Medium
E5: Low
F3: 0.01
F4: 0.02
F5: 0.03
F8: =INDEX($E$3:$F$5,$E$11,2)

Format Control for the Combo Box:
Input range: $E$3:$E$5
Cell link: $E$11
[/tt]
Personally, I would use a Combo Box from the Control Toolbox toolbar, but it's up to you. If you do, the corresponding function for F8 would be:
[tt]
=VLOOKUP($E$11,$E$3:$F$5,2,0)
[/tt]