drop down list displaying number and text but only storing number

[D_Miller]

Hi Excel Gurus,

I want to create a drop down list with a set of values in a range from 1 to 5 but I also want the user to have an explanation of what the range means. E.g"
1 - Very Low
2. - Low
3 - Medium
4 - High
5 - Very High.

I want to show the above in the drop down list. However after the user selects and option I would like the number (1-5) only to be stored in the cell. The reasons for this is I would like to some analysis on the enter numeric scores.

Any assistance will be greatly appreciated.

 

[DanteAmor]

For example, you can have in cell D4 your list and in another cell, D3, a formula to obtain the number:

Dante Amor.xlsm
	A	B	C	D	E
1
2
3				3
4				3 - Medium
5
6
Hoja7

Cell Formulas
Range	Formula
D3	D3	=LEFT(D4)+0

 

[D_Miller]

Thanks for the feedback Dante,

However I don't want the "-Medium" to show in cell D4 at all.

Is what I am asking even possible in Excel? In Microsoft Access this can be done very easily so I thought it can also be accomplished in Excel.

 

[mikerickson]

Put 1,2,3,4,5 in a column of cells.
Format the first cell with the custom format 0" - VeryLow"
Format the second cell with the custom format 0" -Low"
etc.

Use those five cells as the source for the drop-down. The linked cell will show the underlying value.

 

[D_Miller]

Thanks for the quick response Mike, that is exactly what I was looking for Cheers.

Stay Safe.

D

 

[D_Miller]

Hey Guys I have a related question.

When I make the columns smaller the writing in the list also shrinks so that the persons who needs to select the options cannot fully see the text. Is there a way to have the entire list data shown irregardless of the column size?

 

[mikerickson]

I don't know of any way to effect the width of the dropdown box, but if you used abreviation, it would make each list entry narrower
1-v.low
2- low
3-high
4-v.high

 

[D_Miller]

OK Thanks so much Mike.

I come from a MS Access background and you can actually do this in there. Oh well.!

Thanks for all you great help already.

 

[mikerickson]

Excel is a grid, a spreadsheet. Maintaining that grid i.e. keeping every cell in the proper column and row is an overriding feature of excel. Widening the dropdown would put it into the next column. That probably why Access has that feature but Excel doesn't

 

[D_Miller]

Thanks for the feedback.