Drop Down list with Code Description

[BigFish311]

I was looking for a way to show a code description when choosing from
multiple values in a single cell.

Example

I have these codes and their definitions in a spreadsheet
A - Architecture
C - Civil
E - Electrical

I only see the following below to choose from in my list which might
not be a big help for some people.
A
B
C

I only want to see A show up in the cell but I would like to see "A -
Architecture" show up in the drop down list.

Can this be done?

Thanks

 

[John C]

On your Data Validation, there are 3 tabs. The second tab is the INPUT
message. you can give it a name, and put descriptive information in the box.
In addition, there is a 3rd tab, Error Alert, where if they try to put
incorrect data, you can tell xl what to say is the error, how to correct,
basically anything you want.

 

[Otto Moehrbach]

With VBA you can have the drop-down list show the whole thing (A -
Architecture for instance), then show just the "A" after a selection is
made. You would setup the Data Validation to not display an error alert
when a wrong entry is made. A macro like the following would work. This is
a sheet event macro and must be placed in the sheet module of your sheet.
To access that module, right-click on the sheet tab, select View Code, and
paste this macro into that module. "X" out of the module to return to your
sheet.
You will need to add all the other list items and the corresponding single
letter that you want in the code. Post back if you need more. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
If IsEmpty(Target.Value) Then Exit Sub
Application.EnableEvents = False
Select Case Target.Value
Case "A - Architecture": Target.Value = "A"
Case "C - Civil": Target.Value = "C"
Case "E - Electrical": Target.Value = "E"
End Select
Application.EnableEvents = True
End If
End Sub

 

[BigFish311]

With VBA you can have the drop-down list show the whole thing (A -
Architecture for instance), then show just the "A" after a selection is
made.  You would setup the Data Validation to not display an error alert
when a wrong entry is made.  A macro like the following would work.  This is
a sheet event macro and must be placed in the sheet module of your sheet.
To access that module, right-click on the sheet tab, select View Code, and
paste this macro into that module.  "X" out of the module to return to your
sheet.
You will need to add all the other list items and the corresponding single
letter that you want in the code.  Post back if you need more.  HTH  Otto
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If IsEmpty(Target.Value) Then Exit Sub
        Application.EnableEvents = False
        Select Case Target.Value
            Case "A - Architecture": Target.Value = "A"
            Case "C - Civil": Target.Value = "C"
            Case "E - Electrical": Target.Value = "E"
        End Select
        Application.EnableEvents = True
    End If









- Show quoted text -

Thanks, worked like a charm

 

[Otto Moehrbach]

You're welcome. Thanks for the feedback. Otto

With VBA you can have the drop-down list show the whole thing (A -
Architecture for instance), then show just the "A" after a selection is
made. You would setup the Data Validation to not display an error alert
when a wrong entry is made. A macro like the following would work. This is
a sheet event macro and must be placed in the sheet module of your sheet.
To access that module, right-click on the sheet tab, select View Code, and
paste this macro into that module. "X" out of the module to return to your
sheet.
You will need to add all the other list items and the corresponding single
letter that you want in the code. Post back if you need more. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
If IsEmpty(Target.Value) Then Exit Sub
Application.EnableEvents = False
Select Case Target.Value
Case "A - Architecture": Target.Value = "A"
Case "C - Civil": Target.Value = "C"
Case "E - Electrical": Target.Value = "E"
End Select
Application.EnableEvents = True
End If









- Show quoted text -

Thanks, worked like a charm