Drop Down List with a 2nd Column for Information Only

[gmconrad]

I have a dropdown list in my spreadsheet that identifes a code. The code does not mean anything to the users of the spreadsheet so I want to include a description with each item on the list, BUT I do not want this description to be included when the user selects the code.

For example, I want the user to select from the following items: VP, IM, CF. When they are viewing the choices, I would like them to see a 2nd column to describe the 1st: Vendor Purchase, Internal Manufacturing, Customer Furnished. When they select the item, I only want the 2 digit code to appear though.

Any help without having to do much VBA?

 

[srikanth.nm]

Create a list of your descriptions on a separate sheet. Click on each description cell and go to the Name Box (alternatively Insert->Name->Define) and type the name of the desired code. Then on your main sheet, if the user chooses a code in say A2, type the formula =INDIRECT(A2) in cell B2 to retrieve its description.

 

[jc.021286]

Use Vlookup
So have the two listed, Vlookup the code and have it show the comments that you have prepared.

Regards,
jc

 

[gmconrad]

thanks for the responses but that's not what I'm looking for.

On my main form, I have one column where the user can select a code. In the drop down, I would like the user to see the following:
col 1 col 2
VP Vendor Purchase
IM Internal Manufacturing
CF Customer Furnished

When the user selects 'VP Vendor Purchase", I only want the 1st column's value (VP) to appear on the form. I do not want to see the description on the form (I only want the user to see the description when they are tying to make a selection from the list).

 

[srikanth.nm]

Create your list entries like VP Vendor Purchase .. etc. and use Data validation to display the list. Then, paste this code in your worksheet's Change Event. Replace B:B with the column in which your list is getting displayed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count = 1 Then
If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then
Target.Validation.Delete
Target = Left(Target, InStr(1, Target.Value, " "))
End If
End If
End Sub

 

[gmconrad]

thanks for the help but I don't work with macros. What do you mean by my worksheet's Change Event?

 

[srikanth.nm]

Right-click where Excel shows the sheet's name at the bottom and choose View Code. It takes you to the Visual Basic editor window. You should see 2 dropdowns. In the first, choose Worksheet. In the second, choose Change. You should see this blank worksheet macro inserted:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Paste the previous code starting from Dim till the final End If into the above blank macro.