Data Validation list using multiple columns, but only show 1st

Question

I want to create a range of cells, with Data Validation that shows a list from multiple columns. In my example, the data in the column is a Part Number, and the Description.  I want to show the user both the Part No. and the Description at the same time.  But, once they make their selection, I only want the Part No. on my form.

I have done this by adding an additional column next to my source data.  It simply concatenates the other two columns, and this concatenated list is what populates the pop-up window that the user selects from.

However, I have been unable to figure a way to only have the Part No. end up in the cell.  I tried VLOOKUP, but that required a circular reference, and so it was not possible.  Another approach is to insert an AxtiveX control. But, that presents problems when the user wants to add more to the list of inputs, and more rows to the number of parts that are ordered. VBA is also out of the question. 

My next thought, was to use a formula, so that only a fixed number of characters are displayed or that the display text is truncated after a certain marker.  The problem with this is that the part numbers and descriptions are fairly free-form.  I can't depend on any particular character being a valid delimiter.

Is what I want to do possible in Excel 2013?

Attached is an image that should explain what I want to do.

Thank you for any advice.

-joe

Answer 1

Yes, it is easy to do using event code. This example was written to match your specific structure, but with the lists on another sheet named "Lists"

1. Copy this code.
2. Right-Click the sheet tab of interest.
3. Select "View Code"
4. Paste the code into the window that appears.
5. Save the file as a macro-enabled .xlsm file.
6. Make changes as needed

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim v As Variant

    If Target.Cells.Count > 1 Then Exit Sub

    If Intersect(Target, Range("E3:E9")) Is Nothing Then Exit Sub 'specific range

    'Turn off events to keep out of loops

    Application.EnableEvents = False

    v = Application.Match(Target.Value, Worksheets("Lists").Range("C:C"), False)

    If Not IsError(v) Then

        Target.Value = Worksheets("Lists").Range("A:A").Cells(v).Value

    End If

    'Turn events back on to get ready for the next change

    Application.EnableEvents = True

End Sub

Answer 2

Thank you for your effort, but I was trying hard to avoid involving any VBA or macros. It is not that I, personally, have a problem with them, but using them involves lowering the security settings. This is not something I want to do company-wide.

-Joe

Answer 3

What you want to do requires VBA of one form or another. And using macros does not require lowering the security level - store the workbook in a trusted location. You shouldn't just ignore the single best productivity feature of Excel.

Answer 4

I had come across one web site that had proposed a non-VBA solution. But, when I tried to make it work, I was unsuccessful. Then, I figured that I should post it here, in case someone else understood how it worked, and why it failed.  However, after spending an hour trying to re-discover it, I gave up.  But, at least one person thinks it is possible.

I have adopted the VBA approach, and I will just have to have work with users get this to work on their system.

Thank You,

-Joe