Select from Dropdown List and Concatenate Chosen Values Separated by Comma

nerdy_nerdsalot

New Member
Joined
Oct 24, 2015
Messages
2
Hi all, I have created a table of names on Sheet 1 called 'List'. On Sheet 2, I have created columns 'Task' and 'List'. I would like to choose multiple names in the list column drop-down and have them concatenated separated by commas in the same cell or an adjacent cell. Is this possible? How can this be done? I'm open to any solutions, preferably the simplest, including basic Excel functions or VBA scripts. Also, I'm open to changing the general structure of the sheet if what I'm asking for can be done in an alternative way

I'm using Data Validation for the drop-down list, however this only allows me to choose one name from the list at a time (unless I were to create another value with the names already concatenated which would defeat the purpose!)

Thank you,

Nerdy Nerdsalot
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

mr2bart

Board Regular
Joined
Dec 18, 2014
Messages
56
Hi,
by drop-down list, only way is what you did: already concatenated datas.
From my opinion, it's the best and to concatenated, there maybe you need VBA depending your datas.
 

nerdy_nerdsalot

New Member
Joined
Oct 24, 2015
Messages
2
Hi,
by drop-down list, only way is what you did: already concatenated datas.
From my opinion, it's the best and to concatenated, there maybe you need VBA depending your datas.

Thanks mr2bart. To give a more specific example with data that captures the essence of what I'm looking for, please see below:

List
Albert Apple
Billy Bob
Cathy Catherston
David Daniels
Esther Estelle

<colgroup><col></colgroup><tbody>
</tbody>

Task below begins in cell C4

Task
ListNotes
Buy lightbulbAlbert AppleD5 needs to be "Albert Apple, Billy Bob" by choosing multiple drop-down list items
Wait for lightbulbCathy CatherstonD6 needs to be "Cathy Catherston, David Daniels, Esther Estelle" by choosing multiple drop-down list items
Screw in lightbulbBilly BobD7 needs to be "Billy Bob, Esther Estelle" by choosing multiple drop-down list items
Turn on lightbulbCathy CatherstonD8 needs to be "David Daniels, Cathy Catherston" in that order by choosing multiple drop-down list items
PartyAlbert AppleD9 needs to be everyone separated by commas by choosing multiple drop-down list items

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Thanks and best regards,

Nerdy Nerdsalot
 

TrumpExcel

Board Regular
Joined
Aug 1, 2013
Messages
61
You can use the following VAB code to do this:

Private Sub Worksheet_Change(ByVal Target As Range)


Dim Oldvalue As String
Dim Newvalue As String


On Error GoTo Exitsub


If Target.Address = "$C$2" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else


Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If


Application.EnableEvents = True


Exitsub:
Application.EnableEvents = True


End Sub

You would need to put in the cell address of the cells that have the drop down list in it. I have used C3 in this code.

Here is the download file --> Download

This is a worksheet change event so it would go in the code area for the worksheet.
You can read more about it here --> Select Multiple Items from a drop down list
 

Watch MrExcel Video

Forum statistics

Threads
1,122,894
Messages
5,598,724
Members
414,253
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top