New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Selecting more than one entry from a cell drop down list

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Selecting more than one entry from a cell drop down list

Postby lmartin » Mon Jan 02, 2012 9:32 pm

I have created a drop down list in a cell, however it only allows me to select one entry. Is it possible to select more than one entry from a drop down menu in any one cell?

Thank you for your assistance,

Lucy
  • 0

lmartin
Rookie
 
Posts: 2
Joined: Jan 2, 2012
Reputation: 0

Re: Selecting more than one entry from a cell drop down list

Postby Don » Mon Jan 02, 2012 11:46 pm

Hi and welcome to the forum!

If you are using straight-up data validation to achieve the drop-down menu effect, I don't think you will be able to get the multiple item select functionality that you're looking for. You can do this with a List Box in the Form controls section. If you're in Excel 2007 or later, go to the Developer tab > Insert > and select List Box. Then insert the List Box, right-click it, select Format Control, go to the Control tab, choose an input range (range of cells in the spreadsheet that contain the list), the select "Multi" from the Selection type options group.

But, why do you need to select multiple options? Maybe there is an easier way to achieve the desired result.
  • 0

Don
Moderator
 
Posts: 733
Joined: Dec 4, 2011
Reputation: 2
Excel Version: 2010

Re: Selecting more than one entry from a cell drop down list

Postby lmartin » Wed Jan 04, 2012 7:17 pm

Thanks for your help with my issue. Unfortunately I am not working with excel 2007, it is 2003 version!
I am using a spreadsheet to input data from schools, there are over 100. I want to be able to choose from a list of objectives for each school. There is a list of 8 of them and each school has between 1 and 8 indictated. Rather than copy and paste or typing them out each time. I wanted to have a drop down list for each school so I could click on the appropriate objectives for each. I hope that makes sense, thanking you once again.
  • 0

lmartin
Rookie
 
Posts: 2
Joined: Jan 2, 2012
Reputation: 0

Re: Selecting more than one entry from a cell drop down list

Postby charles » Wed Jan 04, 2012 7:55 pm

Hi,

If you can put together a copy of your workbook and post it to this forum we may be able to help you better.
You must be logged in and when you reply to this post you will "Upload attachments". You will then be able to select the file and add it to your post. As Don mention you also with 2003 have the option for "Listbox". You select "View", "ToolBar", and the select either "Control Toolbox" or "Forms". You should be able to select the "List" box function there. You will need to scroll your mouse over the option and it will display the form names.
THat being said you may want to conceder a Userform with with with drop downs. With it you can set command button to Add/Delete.
  • 0

There are other ways too do this but...

Be kind let us know if you posted somewhere else!



Charles
charles
Excel Badass
 
Posts: 632
Joined: Dec 10, 2011
Location: Mississippi
Reputation: 1
Excel Version: Excel 2003, 2007 and Mac 2011

Re: Selecting more than one entry from a cell drop down list

Postby Sisyphus » Wed Jan 04, 2012 8:32 pm

Hi,

This post was almost done when Charles posted his reply. Any duplication is purely accidental and merely confirms that he is right i the first place.

Listboxes are available in Excel 2003, too. Bear in mind, however, that they are user interface tools in either version, meaning that they transport data between the user and a program that processes them. I think you have the user but what about the program? Perhaps the need will become more clear as you go along.

Start with a blank worksheet.
Enter your list of 8 choices (or more), vertically, in a column of adjecent cells.
Name the list as something, why not "List". To do so, select the range containing your list and write the name in the Name Box at the top left of your screen. Remember to "Enter".

Right-click anywhere in the blank area of your toolbar.
From the dropdown that appears select ControlToolbox.
The "Toolbox" is a toolbar which opens floating. You can drag it to a side or edge if it bothers you.
Find the icon for the "Listbox" in it and click on it.
Draw a rectangle of the size of the listbox that you want somewhere on your sheet.
The Listbox will be created as you release the mouse button.
It will be selected. You can delete it. Or you can drag its handles to adjust the size.
The most important part of it isn't even on the Listbox itself. It is the button on the Control Toolbox in the upper left corner. When you hover the mouse over it, it says "Exit design mode". When you exit design mode the listbox becomes functional. While in design mode you can modify it. You can toggle this button on and off.

Right-click anywhere in your Listbox while in Design Mode.
Select Properties from the dropdown.
If you know what you are looking for, use the alphabetical listing. For fishing exepitions you may prefer categorized listing of the properties. Select the tab you want. The number of items isn't smaller in either display, but you don't need to set them all.
Enter the name of your list against ListFillRange (Now you see your list in the box).
Set MultiSelect to fmMultiSelectMulti (this is a dropdown selection)
Set ListStyle to fmListStyleOption (another dropdown)

That is all. You can play around with the other properties if you are so inclined. But if you exit design mode now you have a fully functional Listbox that does exactly what you want it to do.

If you get this far you may come back to this forum to get some help on filling the data from the listbox to the worksheet where you want it. :lol:
Have a great day!
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Selecting more than one entry from a cell drop down list

Postby haznavy » Thu Apr 19, 2012 2:38 pm

Don wrote:Hi and welcome to the forum!

If you are using straight-up data validation to achieve the drop-down menu effect, I don't think you will be able to get the multiple item select functionality that you're looking for. You can do this with a List Box in the Form controls section. If you're in Excel 2007 or later, go to the Developer tab > Insert > and select List Box. Then insert the List Box, right-click it, select Format Control, go to the Control tab, choose an input range (range of cells in the spreadsheet that contain the list), the select "Multi" from the Selection type options group.

But, why do you need to select multiple options? Maybe there is an easier way to achieve the desired result.


*********************************************************************************************************************************************
I am having a similiar problem in 2007. What is the simple workaround to be able to select multiple options from a drop down to populate a cell? I need to be able to select the big bucket item and drill down to the smallest component(s) and capture all the data elements. Thank you for any assistance.
  • 0

haznavy
Rookie
 
Posts: 15
Joined: Jan 26, 2012
Reputation: 0


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 55 guests