Populate a single sheet with multiple entries from a drop down list

Hi this is my first posting on here so I am hoping someone is kind enough to help me. I am OK at putting a drop down validation list in my Excel 2007 spreadsheet but what I want to do is be able to chose multiple choices and these choices made populate a single cell followed by a comma each time a choice is made.
Sorry the title should read Single Cell not Single Sheet !! oops a senior moment

Zuerner,

Welcome to the forum!
So if you have headers in row 1 and actual data starts in row 2, and the drop-down validation lists are in columns, A B and C, then in cell D2 and copied down you could have:

Please Login or Register  to view this content.

Is something like that what you mean?

I'm still a little confused so...

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
To attach a file, click the "Go Advanced" button and then click the paperclip icon to open the Manage Attachments dialog.

Martin,

I think I get it now. You want to choose multiple names from the drop-down list to populate the cell the drop-down list is in. If that is the case, you're going to have to use VBA. Are you ok with a VBA solution?

Martin,

Attached is a modified version of your sample file. I have made a lot of changes.
First of all, you'll notice the 'Rota' sheet now has a different layout. You can enter the date of the Week Commencing in cell B1, and the days of the week in B3:F3 will automatically update. They are formatted to show the day of the week as well as the date.

Secondly, I renamed sheet 'Data' to be 'Lists'. In B1 is the header "Names" and starting in cell B2 and going down without skipping any rows (so no blanks in the list) is the list of names. I also edited the named ranged "names". It is now named "list_Names" and is a dynamic named range that is defined with this formula:

Please Login or Register  to view this content.

The reason it uses column B is because column A is hidden. It is a reserved column for the userform so that it can sort the name lists as names get shuffled around between assigned and unassigned.


Next is where the VBA stuff starts. I used the worksheet_selectionchange event for sheet 'Rota'. The event will only trigger when a single cell withing B4:F6 is selected. Techincally its B4:F(last row), but the last row in this example is 6. The code used for the selectionchange event is:

Please Login or Register  to view this content.

What that code does is pull up the userform named frm_AssignDuty. That is a userform I created that contains 2 listboxes and some buttons. The left-hand listbox is the list of unassigned names. The right-hand listbox is the list of assigned names. So the form opens, you can choose whichever unassigned names you want and move them over to the assigned names and click OK, which will fill the selected cell with the list of names and close the form. You don't have to use the ">" and "<" buttons to move names over, you can also double-click on a name and it will get moved that way, so both options are available (double-clicking and using the buttons). Pressing cancel will close the form without making any changes to the cell. The list of unassigned names is based on the list of full names and then it removes any names that have already been assigned in the same column. For example, if in cell B4 you assign Name01 and Name03, then in cell B5 those two names will no longer be available because they have already been assigned. If you want to free a name up again, click on cell B4 and unassign the name.

Here is the full code used by the userform:

Please Login or Register  to view this content.

That's fantastic thank you so, so much, it's so much better doing it your way than the way I was thinking of doing it, or rather hoping to do it! Thanks again..Martin

The code will already expand dynamically. Its based off of the contents in column A. So if column A is populated, that row will trigger the code.