Drop Down List With Details

[CHeil402]

I'm not quite sure how to accomplish this, but I want to have a drop down list with abbreviations and details, but when you pick an option, only the abbreviation goes into the target cell. For example if I made a drop down list that had state abbreviations AND their descriptions (PA-Pennsylvania, VA-Virginia, MD-Maryland, etc...) but wanted only the abbreviation to be selected (PA, VA, MD) once you make a selection. Is there a way to do this? I have the abbreviations in one column and the descriptions in another, but so far I've only been able to make a list of one or the other, not both.

 

[cornflakegirl]

I don't think there's an easy way to do that.

Could you instead have say the dropdown in A1 returning the abbreviation and description - and then in A2 put = LEFT(A1,2) - that would give you just the abbreviation, and you could drive whatever you needed from that?

 

[Andrew Poulsom]

You could try this in the module for the Worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Address = "$A$1" Then
        Target.Value = Left(Target.Value, 2)
    End If
    Application.EnableEvents = True
End Sub

Change the reference to $A$1 to refer to your data validation cell.

 

[cornflakegirl]

Andrew - that's so cool - I just assumed that doing that wouldn't work. Why doesn't the data validation object to it?

 

[Andrew Poulsom]

Andrew - that's so cool - I just assumed that doing that wouldn't work. Why doesn't the data validation object to it?

Unlike Protection, Data Validation applies only to the user interface, not to VBA.

 

[cornflakegirl]

That's good to know - thanks!