May 04 2018
07:53 PM
- last edited on
Jul 31 2018
08:02 AM
by
TechCommunityAP
May 04 2018
07:53 PM
- last edited on
Jul 31 2018
08:02 AM
by
TechCommunityAP
May 05 2018 04:43 PM
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)
May 05 2018 06:53 PM
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
May 05 2018 10:25 PM
SolutionHi 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
May 06 2018 12:04 AM
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
Jun 03 2020 03:43 AM
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.
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 !
Jun 03 2020 03:47 AM
May 05 2018 10:25 PM
SolutionHi 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