Multiple Pick-List Boxes???

[SDThomas99]

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!

 

[jbeaucaire]

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

 

[SDThomas99]

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?

 

[jbeaucaire]

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:

	 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.

 

[SDThomas99]

THIS IS SO COOL! Thank you very much for your help!!!

I actually used _005.

 

[jbeaucaire]

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))

 

[SDThomas99]

I will give that a try.

Thanks again for the much needed help!