Multiple Pick-List Boxes???

SDThomas99

New Member
Joined
Sep 29, 2009
Messages
14
I have a huge workbook with many hidden worksheets where data on a form (worksheet #1) pulls data from. The form has a picklist that has over 400 names (currently, when a name from the picklist is selected the data on the form updates for that person). My task is to break it up to where it is easier to manage. I need to be able to make a picklist or something like it where I can choose a department - once the department is chosen a list is presented with employees in that department. Once the employee is chosen, then the data in the form updates for that employee.

Is there an easy way to do this?

Your help is greatly appreciated!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
DEPENDENT DROP BOXES
These are called Dependent Drop Down lists and they use named ranges and INDIRECT() references in the proceeding drop boxes.

Here's a sample sheet I've posted showing how it's done. The sheet "CHOICES" has the dependent drop lists (and shows one of the dangers of using them) and the sheet "LISTS" shows the lists and named ranges in use.

Have a look.

Sample Files
http://www.excelforum.com/attachmen...dependent-drop-down-lists-dependentlists3.xls 3 levels
http://www.excelforum.com/attachmen...237582582-drop-down-menus-dependentlists2.xls 2 levels
http://www.excelforum.com/attachmen...-validate-list-dependentlists-selfsorting.xls Self-Sorting lists of fruits by color choices

More reading:
http://www.contextures.com/xlDataval02.html
http://www.contextures.com/xlDataVal13.html

'Unique Values from Repetitive data in a drop box
http://www.excelforum.com/attachmen...75327-dropdown-menu-unique-validationlist.xls
 
Upvote 0

SDThomas99

New Member
Joined
Sep 29, 2009
Messages
14
I have found your links to be very helpful - thank you. My problem (now that I actually get to work on it) is that my second list is numbered districts - it will not let me name the supporting Named List "005" This is what the managers know them by. . . Is there a way around this?
 
Upvote 0

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Let's say that your drop down in B1 has a value of 005...that won't do for an INDIRECT() named range reference. SO we can cheat. You can create a table on your sheet somewhere with conversion information. Let's say the table is like so:
Code:
	 M	  N
1     District	 List
2	005	ListFive
3	006	ListSix
4	007	ListSeven
5	008	ListEight
6	010	ListTen
7	013	ListThirteen
In another cell on the same row, you could use a LOOKUP to convert the option chosen in cell B1 into a text string you CAN use for an indirect listing, perhaps in C1 (and hidden):

=IF($B1="","",INDEX($N$2:$N$7, MATCH($B1, $M$2:$M$7, 0))

That will cause "ListFive" to appear in that hidden cell when 005 is chosen from the B1 drop box. Now your next drop list has a cell with a usable text string in it for the next dependent list.
 
Upvote 0

SDThomas99

New Member
Joined
Sep 29, 2009
Messages
14
THIS IS SO COOL! Thank you very much for your help!!!

I actually used _005.
 
Last edited:
Upvote 0

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
If you really wanted to, you could probably finesse the INDEX/MATCH into the data validation itself to skip needing that hidden cell. Something like:

=INDIRECT(INDEX($N$2:$N$7, MATCH($B1, $M$2:$M$7, 0))
 
Upvote 0

Forum statistics

Threads
1,187,091
Messages
5,961,527
Members
438,552
Latest member
Robyrubyjane

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