Excel Drop Down Multi-Select Update

I've done another update to the Data Validation Multiple Selection sample, thanks to a question from Pat, in a blog comment. He has 3 columns with different drop down lists, and wants to add new items to the applicable lists.

I can get 3 separate columns to each allow multiple selects from their own lists and to allow write in contents...But what I want ...is to make the selected columns (not all) so that write in selections are written to the lists and the lists are sorted.

So, in the new example, on the SameCellAddSort worksheet in the sample workbook, that's what happens. You can do a multiple select in columns C and D, but only a single selection in column B.

datavalmulticelladdsort01

Add New Items

New items can be added to the Names and Numbers columns. If a new name is entered in column C, is will be added to the NameList range on the Lists sheet. Then, the list is sorted alphabetically.

datavalmulticelladdsort02

Download the Sample File

To see the code, you can download the Data Validation Multiple Select sample file. The file is in Excel 2003 format and zipped. There are macros in the file, so enable them when opening the workbook.

Watch the Multiple Selection Add Sort Video

To see how the multiple selection add and sort technique works, and see an explanation of the code, you can watch this Excel video tutorial.

Or watch the video on YouTube: Data Validation Multiple Selection Add Sort

More Information on Data Validation Multiple Selection

Most of the examples in the DataValMultiSelect file have limits set in the code. For example, the code might check the column number, and only allow the multiple selections if the column number is 3.

If you remove those checks from the code, and just check for data validation in the selected cell, you could have several multi-selection lists on the same worksheet. There is an explanation on my Contextures website, for adjusting the code:

Excel Data Validation - Select Multiple Items

Here are a few more article on the data validation multiple selection technique:

How to Set up Multiple Selection Excel Drop Down

Edit Multiple Selections in Excel Drop Down Lists

Select Multiple Items from Excel Data Validation List

 

_______________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

8 comments to Excel Drop Down Multi-Select Update

  • Betsy Barber

    I was pleased to be able to set up a multi-select drop-down for a single column on one Sheet based on your code. However, someone wants to have another column on the SAME sheet also be multi-selectable. I.E., in Column A, select on models of dream cars you'd like to have. Column B is select on the cars you can afford.

    Adding the code in twice and changing the column number for the second instance didn't work.

    Is this possible? Or, do I have to create a On Event macro to take the person entering to a different sheet, then populate the original sheet with the multiple selections?

    Thanks for any help you can give me.

    Betsy

    • @Betsy, most of the examples in the DataValMultiSelect file have limits set in the code. For example, the code might check the column number, and only allow the multiple selections if the column number is 3.

      If you remove those checks from the code, and just check for data validation in the selected cell, you could have several multi-selection lists on the same worksheet. There is an explanation on my Contextures website, for adjusting the code:

      Excel Data Validation - Select Multiple Items

  • Betsy Barber

    Deb - It worked right out of the gate. Thanks!!!

  • Betsy

    Someone suggested one step further - use Conditional Formatting on the columns that should NOT be multi-selectioned since now all of them have the capability. That worked well, too.

    Any cell with a comma (separating the selections) changes colors. (I just have to insure that none of the selections have commas in their name.)

  • Awais

    Debra,

    This is fantastic! Following on from Betsy I was wondering whether the code can be modified to limit multi-selection for specific columns? I have drop down lists in a number of columns on a work sheet but only need the multiselect in a few of them.

    I had modified the code to

    If Target.Column = 3 Or 5 or 7 Then

    but this seemed to allow multi-selection in all columns instead of restricting it to the specific columns 3, 5 and 7. I'm guessing it's not that simple...?

  • Asad Saifi

    Hello! Pl refer your 'SameCellAddRemove' sheet in 'DataValMultiSelect' excel. This is amazing and fantastic excel. In that sheet I want that after selecting the data it should be sorted out within the cell. I mean if I am selecting One, Three & Four so it should come in its sequence only in any condition. If I am selecting 'One' then 'Three' & 'Four' so it takes it correct - One, Three, Four but when i deselect One and again select it then it comes like this - Three, Four, One. So I want that it should be sorted out. Hope you understood my query. Thanks in Advance.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>