# Drop-down list with names shown, but produces a numerical value in the cell for a formula

#### TheUnconquered

##### New Member
Hello,

I have created a drop-down list with five names or words. I wanted to see if there's a way to have the names show up for the user, but have a numerical value in that cell that would be used in a formula.

Thanks,
Blake

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
You could use a helper cell that returns the number.

You could use a helper cell that returns the number.
Can you explain a little further? Are you talking about separate cells that have the numbers that correspond with the names and then if xyz from the drop-down menu is used then the formula would pull the corresponding number?

Just depend on your data & what you are trying to do, but a lookup table would be one option.

Just depend on your data & what you are trying to do, but a lookup table would be one option.

Here's a pic. In A31 where it says Sedentary I would like it to show Sedentary for whoever is using it, but was trying to get the value of the cell to be 1.2 so I can use A31 in the formual in A34. I can link the formula off to the side in J, but was just trying to reduce the info off to the side.

How many values do you have in the drop down?

How many values do you have in the drop down?
Five. It's the five terms over in column I (except the 5th is supposed to say Extremely Active vs having two Very Active's). Then in column J is the corresponding factor I need for my formula.

In that case you could use a nested if like
Excel Formula:
``=IF(A31="sedentary",1.2,IF(A31="Lightly Active",1.375,IF(A31="Vey Active",1.7,"")))``
and have that in A32, but I would stick to the lookup table & put a vlookup function in A3

You could put the VLOOKUP in the A34 formula:

Book1
ABCDEFGHIJ
30Activity LevelSedentary1.200
31SedentaryLightly Active1.375
32Moderately Active1.550
33Output:Very Active1.700
342060Extremely Active2.000
Sheet36
Cell Formulas
RangeFormula
A34A34=1716.667*VLOOKUP(A31,I30:J33,2,0)
Cells with Data Validation
CellAllowCriteria
A31List=I30:I34

Thanks to both of you for helping out here. Appreciate it.

Replies
1
Views
151
Replies
2
Views
490
Replies
1
Views
80
Replies
7
Views
163
Replies
2
Views
106

1,203,326
Messages
6,054,747
Members
444,748
Latest member
knowak87

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back