Data Validation List with description

[nekthen]

I think this ought to be possible, but maybe Excel does not support it.

I want to enter a part code. Easy enough, I make a list and do a data validation.

However, not all my users know all the part numbers so I want the drop down to display the code and its description, but only put the code in the cell.

Anyone got a hack for this?

 

[John_w]

This is possible with the Worksheet_Change event handler. Assuming the data validation cells are in column K and the part number is separated from its description by a semi-colon, put this code in the sheet module (see below) of the sheet containing the data validation cells:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim p As Long
    If Not Intersect(Target, Columns("K")) Is Nothing And Target.CountLarge = 1 Then
        p = InStr(Target.Value, ";")
        If p > 0 Then
            Application.EnableEvents = False
            Target.Value = Left(Target.Value, p - 1)
            Application.EnableEvents = True
        End If
    End If
End Sub

To open the VBA editor in the correct sheet module, right-click the sheet's tab and click View Code.

 

[HighAndWilder]

I think this ought to be possible, but maybe Excel does not support it.

I want to enter a part code. Easy enough, I make a list and do a data validation.

However, not all my users know all the part numbers so I want the drop down to display the code and its description, but only put the code in the cell.

Anyone got a hack for this?

I find the Validation List OK if there are a few options but when there are many and users may want some additional help in finding the right one then I use a Userform
such as the one in the image.

Instead of scrolling through vast numbers of options, the Userform can have a number of ways to filter the list. In this case the first letter of the description is used but
it could by product category, type or application or one could drill down through various layers to display the right list.

When the used selects a cell into which the code is to be written the appropriate Userform is displayed.

When an option is selected the code is placed into the active cell. In this case, the description is also placed in column B.

Users can be prompted if they try to replace a previously seleced option.

The userform can be moved by the user but have a default location on the screen.

The Userform can stay visible after an option is selected or disappear.

The active cell can change to where data entry by the user is needed next.

Such a userfom can be used for any type of data entry. It is good for date entry where precision is required.

An application can have any number of userforms for data entry.

Could this be useful?

 

[nekthen]

I will definitely have a look at user forms. I cobbled together a solution, which works but is far from elegant

I made a list in a column concatenating the Code a " ; " and the description.

I set the width of the cell to hide the semi colon, this will give a problem with variable lengthcodes!

=IF($A5="","",XLOOKUP(TEXTBEFORE($A5," ;"),Sheet1!$A$14:$A$429,Sheet1!$B$14:$B$429,""))

The textbefore function is very nice and allows xlookup to find the description and other information in the original database