Dropdown box question

D

David

In the Input Range of a drop down box is there a way to concatenate two
columns? To be specific:



Have Product #'s in Column A of the Input Range e.g. 8755

Have Product Desc in Column B of the Input Range e.g. 90 Minute Video Tape



Want both columns in range concatenated with a dash inbetween, so it appears
in the drop down box as: 8755 - 90 Minute Video Tape



Thanks in advance.



David
 
F

Frank Kabel

Hi
I would use a helper column with a formula like
=A1 & "-" & B1
and use this helper column as source for your drop down
 
D

David

Frank -

Thanks for the response, what you said works. However, within the scope of
this project, it would be better if there is a way to accomplish this
without the "helper column".

David
 
D

Dave Peterson

Do you really mean a dropdown from the Forms Toolbar?

If yes, you could use a macro to populate that dropdown:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim Wks As Worksheet
Dim myDD As DropDown

Set Wks = ActiveSheet
With Wks
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set myDD = .DropDowns("drop down 1")
myDD.RemoveAllItems
For Each myCell In myRng.Cells
myDD.AddItem myCell.Value & " - " & myCell.Offset(0, 1).Value
Next myCell
End With

End Sub
 
Top