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

[TheUnconquered]

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

 

[Fluff]

You could use a helper cell that returns the number.

 

[TheUnconquered]

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?

 

[Fluff]

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

 

[TheUnconquered]

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.

 

[Fluff]

How many values do you have in the drop down?

 

[TheUnconquered]

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.

 

[Fluff]

In that case you could use a nested if like

=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

 

[Eric W]

You could put the VLOOKUP in the A34 formula:

Book1
	A	B	C	D	E	F	G	H	I	J
30	Activity Level								Sedentary	1.200
31	Sedentary								Lightly Active	1.375
32									Moderately Active	1.550
33	Output:								Very Active	1.700
34	2060								Extremely Active	2.000
Sheet36

Cell Formulas
Range	Formula
A34	A34	=1716.667*VLOOKUP(A31,I30:J33,2,0)

Cells with Data Validation
Cell	Allow	Criteria
A31	List	=I30:I34

 

[TheUnconquered]

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