Creating a 2-column drop down combo box in a cell

[Marzy]

How is a 2-column drop down list created whereby one double clicks in a cell opening the 2-column drop down list then populating the cell with the data from column 2?

The first column is the department name, the second column is the dept numbers, by choosing the dept name the cell is populated with the dept number.
This function is repeated over each row.
Would using a VBA make this function possible?

Thank you.

 

[sunnyland]

May be you can try the following to see if it works for you.

Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
ActiveCell.Value = ComboBox1.Column(0, ComboBox1.ListIndex)
ActiveCell.Offset(0, 1).Value = ComboBox1.Column(1, ComboBox1.ListIndex)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'the combo follows your selection in column 1
If Not Intersect(Target, Columns(1)) Is Nothing Then
'if one cell selected
If Target.Count = 1 Then
With ComboBox1
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width
.Height = Target.Height
End With
End If
End If
End Sub