Data validation drop down with two columns

[ejgyp]

I would like to create a drop-down list containing data from two columns in a range. Column A contains a numeric value from 0 to 5, so total 6 rows. Column B contains explanation of each of the values.
I need the user to see both the value and the description, and when selecting the relevant value, the cell will be populated by the numeric value from column A only.

So for example:

I have added the name "Skills" to the above range, and then tried to set this in the data validation module as the List entry:

Since the data range is on a separate worksheet named "Grading", I also tried to add the worksheet name "=Grading!Skills" but this did not work either:

I do not really want to go down the VBA route, but if I have to I can. Does anyone have any idea how to overcome this?
If I only have the VBA option, would appreciate some assistance on this as well.

One more thing... Once this is working, I will need the same validation to be active on a large range of cells (E3:AF20) and a number of worksheets in the same workbook. Not sure this influences the situation, but thought to mention it anyway.
Thanks

 

[sykes]

Whilst it's not ACTUALLY a data validation solution (even though the author calls it that) this would be a solution for you.

 

[bobsan42]

If the name Skills you have defined has a scope Workbook, you do not need to include the sheet name in the reference.

The error message on your second screenshot says clearly that you have to use a single column/row range.
So you have to use either the level column or the description column. If you insist on having both - VBA controls and code are your only option.
It is not an overly complicated solution, but your workbook will lnot be macro free.
No-VBA solution is possible, but you have to consider very well:
- do you really need the level number (You may not need it, and you can always obtain it using VLOOKUP and if you don't plan on changing the descriptions)
- do you really need to see the description (my guess is yes)

Is there a particular reason to use the Skill Level number?

Check this solution:

...............xlsm
	A	B	C
1	Description	Level	DataV
2	Not skilled	0	0 - Not skilled
3	In training	1	1 - In training
4	Training completed	2	2 - Training completed
5	Limited experience	3	3 - Limited experience
6	Significant experience	4	4 - Significant experience
7	Professional	5	5 - Professional
Grading

Cell Formulas
Range	Formula
C2:C7	C2	=B2&" - "&A2

Named Ranges
Name	Refers To	Cells
Skills	=Grading!$A$2:$B$7	C2
SkillsD	=Grading!$A$2:$A$7	C2

............................xlsm
	A	B
20	SkillLevel	SkillDescr
21	0	Not skilled
22	4	Significant experience
23	4	Significant experience
24
Sheet2

Cell Formulas
Range	Formula
A21:A24	A21	=IFERROR(VLOOKUP(B21,Skills,2,FALSE),"")

Named Ranges
Name	Refers To	Cells
Skills	=Grading!$A$2:$B$7	A21:A24
SkillsD	=Grading!$A$2:$A$7	A21:A24

Cells with Data Validation
Cell	Allow	Criteria
B21:B24	List	=SkillsD

or this way:

.............................xlsm
	A	B
26	Skills	SkillLevel
27	2 - Training completed	2
28	3 - Limited experience
29	0 - Not skilled
30
Sheet2

Cell Formulas
Range	Formula
B27	B27	=IFERROR(VALUE(LEFT(A27,1)),"")

Cells with Data Validation
Cell	Allow	Criteria
A27:A30	List	=SkillC

 

[Peter_SSs]

You might see if this is any use: Validation list - limit entry to left characters

 

[bobsan42]

Whilst it's not ACTUALLY a data validation solution (even though the author calls it that) this would be a solution for you.

With multiple cells for validating this solution will need some code to change the combobox behaviour depending on the selected cells and to make it feel as actual data validation.
Depending on the selected cell the combobox will have to:
- show/hide if there is or not data validation set
- Change the linked cell to apply the new value
- move around the sheet to match the selection

 

[ejgyp]

Thanks all for the input. However, still does not solve my issue.
In the meantime I had the brainwave to simply change the description to include the single digit code at the beginning as part of the text, and then I work around the two columns issue! Should have thought of that one a long time ago...
So I am now left with the challenge that the user will select the entry which starts with value 2, for example, and I want the cell to be populated with the digit 2 only, without the rest of the text. I tried a vlookup to the second column of the source range, but of course receive a circular reference error since it is sitting on the same cell! Any way around that?

Thanks again

 

[Peter_SSs]

In the meantime I had the brainwave to simply change the description to include the single digit code at the beginning as part of the text, and then I work around the two columns issue! Should have thought of that one a long time ago...
So I am now left with the challenge that the user will select the entry which starts with value 2, for example, and I want the cell to be populated with the digit 2 only, without the rest of the text.

I believe that the link I provided does exactly that. Can you describe in what way it failed to do so?