Data validation drop down with two columns

ejgyp

New Member
Joined
Aug 18, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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:
1629284894619.png


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:
1629285096065.png


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:
1629285214940.png


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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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:
1629287644093.png

...............xlsm
ABC
1DescriptionLevelDataV
2Not skilled00 - Not skilled
3In training11 - In training
4Training completed22 - Training completed
5Limited experience33 - Limited experience
6Significant experience44 - Significant experience
7Professional55 - Professional
Grading
Cell Formulas
RangeFormula
C2:C7C2=B2&" - "&A2
Named Ranges
NameRefers ToCells
Skills=Grading!$A$2:$B$7C2
SkillsD=Grading!$A$2:$A$7C2

............................xlsm
AB
20SkillLevelSkillDescr
210Not skilled
224Significant experience
234Significant experience
24 
Sheet2
Cell Formulas
RangeFormula
A21:A24A21=IFERROR(VLOOKUP(B21,Skills,2,FALSE),"")
Named Ranges
NameRefers ToCells
Skills=Grading!$A$2:$B$7A21:A24
SkillsD=Grading!$A$2:$A$7A21:A24
Cells with Data Validation
CellAllowCriteria
B21:B24List=SkillsD


or this way:
1629288309765.png

.............................xlsm
AB
26SkillsSkillLevel
272 - Training completed2
283 - Limited experience
290 - Not skilled
30
Sheet2
Cell Formulas
RangeFormula
B27B27=IFERROR(VALUE(LEFT(A27,1)),"")
Cells with Data Validation
CellAllowCriteria
A27:A30List=SkillC
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Aug 18, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,376
Office Version
  1. 365
Platform
  1. Windows
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,579
Messages
5,770,962
Members
425,653
Latest member
UNSING

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top