Data Validation - returning a different result from the options displayed

[GeorgeBryant]

I have a timesheet application where I want the users to select from a table of options but have only one column returned in the spreadsheet.
I have a series of 'Service Line Codes' that are numerical (0-50) and each one has a description "example RT Inspection". I have a column in my spreadhseet for the user to enter the Service Line Code but I want them to see both the service line number AND the description in the Data Validation drop down list.

How do I do this?

Thanks

George

 

[dmt32]

Hi,
go to this site:http://www.contextures.com/excelfiles.html#DataVal

and download free workbook DV0005 - Data Validation "Columns"

This provides a working example for what you want.

Dave

 

[GeorgeBryant]

Thanks Dave.

I'm relatively new to code - how would I copy that code into my workbook? I understand I would need to change the references to Worksheets and names - jus tnot sure how to get started

thanks again

 

[dmt32]

Thanks Dave.

I'm relatively new to code - how would I copy that code into my workbook? I understand I would need to change the references to Worksheets and names - jus tnot sure how to get started

thanks again

Hi,
1 - You will need to replicate the worksheet "Codes" in your workbook for your data validation lists
2 - Your Named Range will need to reference the Column that displays the combined lists (Column B in example)
3 - Right Click the worksheet TAB in your workbook data validation applies to Select View Code.
This takes you to the worksheets code page - copy & paste the amended code below to the sheets code page

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim m As Variant
    Dim DataValidationColumn As Integer, DataValidationReturnColumn As Integer
    Dim DataValidationListSheetName As String, DataValidationNamedRange As String
    Dim wsDataValidationList As Worksheet
    
    On Error GoTo errHandler
    
'*****************************SETTINGS***********************************
    DataValidationListSheetName = "Codes"
    DataValidationNamedRange = "ProdList"
    DataValidationColumn = 2
    DataValidationReturnColumn = 3
'************************************************************************
    
    
    Set wsDataValidationList = ThisWorkbook.Worksheets(DataValidationListSheetName)
    
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column = DataValidationColumn Then
        If Len(Target.Value) > 0 Then
            Application.EnableEvents = False
            m = Application.Match(Target.Value, wsDataValidationList.Range(DataValidationNamedRange), 0)
            If Not IsError(m) Then Target.Value = wsDataValidationList.Cells(1, DataValidationReturnColumn).Offset(m, 0)
        End If
    End If
    
errHandler:
    Application.EnableEvents = True
    If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

I have updated code to make it easier for you to amend - Change ONLY those values shown in RED - Hopefully, variable names I have used explain themselves.

Dave