Display the selected text as number on a drop down list

[mishu.pandey1985]

Hi Team,

I have a requirement to show the selected value in a drop down list as number in an excel. The drop down values appear as text or character and when a user selects any of the text value from the drop down list, it should be displayed as number, please help me on this.

[#2]

Hi,

A couple of options for this,  let me know if this is the sort of thing you were thinking of and I can explain how I did them  (I've also attached the file as a demo)

[#3]

Thanks Wyn for your response.

Yes, this is exactly what I have been looking for. Please find attached my version of the sheet, the scenario has been listed below-

On the attached sheet, on column- AE, there are multiple drop down choice list values to select within each of the rows/records.

The requirement here is that when a user selects any of the text values visible within the drop down list values, it should get converted to a number. For example- if the user selects the option as- 1- Agile Development (aka SDLC and scrum), it should get converted to 1. Similarly when the user selects the option as- Altriris Integration, it should get converted to 2 and so on till 73 for the remaining drop down options.

This need to be done on column AE, AG, AI, AK, AM, AO, AQ

Regards,

Mishu

[#4 · Accepted solution]

Hi Mishu

Unfortunately, you can't make it put a different value in the same cell, so the drop down would be in one column and the corresponding number value would be in another column.

So if you pick from the drop down in cell AE4, you then need a formula in AF4 (or out of the way to the far right) that has a formula to pick up the position of that item in the list      = MATCH(  AE4, Sheet2!$G$3:$G$75 ,  0)

What you want is possible but only with getting into the VBA (Macro) side of things, which is a lot more complex to set up properly,  e.g. be able to double click in AE4 and have a pop up box and be able to see the full description, pick one, and the resulting figure of 1 etc to be returned into AE4

[#5]

Thanks Wyn.

Your response and formula has helped me a lot, I truly appreciate your help and support on this.

Please let me know if you come across a solution as depicted by yourself on the trailing response (VBA- Macros) to display the value in the same column (AE). 

Regards,

Mishu

[#6]

HI @Wyn Hopkins I'm having the reverse problem; I mean .. I need to display the selected number as text .... I have a number (response option) and I need to have the corresponding text (option1 caption, option2 ..) for different questions. 

To resolve this I can not use any of your solutions .. I guess ... 

I'll appreciate any help ! 

Here below the source report I need to work on .. 

Response Option	Option1 Caption	Option2 Caption	Option3 Caption
1	Male	Female
3	Less than 30 years old	from 31 to 40 years old	more than 41 years old
1	Daily	On shift

Thank you !

[#7]

Sounds like a different sort of issue, you'd be best off posting it as a new question in the forum