Drop down box help needed

[Ron Abba]

Good day all,
I have a question about using a drop down box that will list items based on another drop down box cells input. On sheet1B2 I have a Drop down box that is used to select trucks in different departments, such as Sani, admin, ect. On sheet4 I have a bunch of services listed by each Department that have to be performed. I can create the drop down box on sheet1B2 that shows the department, but I can not get it to list the services for the chosen Department in sheet1C11 drop down box. I have to use the Drop down box in sheet1C11 to select the type of service that is due based on the department chosen. Thanks for reading.

 

[Toadstool]

Hi Ron Abba,

If you have a smaller list of Departments you could use Named Tables and INDIRECT but you don't supply much information so I'll use a method to build the selection lists and OFFSET to provide the dropdowns.

Sheet4 columns A and B have your Departments and the Services performed by each.

Sheet4 column E builds a list of distinct Departments for that dropdown in Sheet1 B2. The Sheet1 B2 Data Validation list is
=OFFSET(Sheet4!$E$2,,,COUNTIF(Sheet4!$E$2:$E$20,"> "))
so it offers the Sheet4 distinct Depts up to the first blank entry.

Sheet4 column G builds the list of Services based on the Sheet1 B2 Dept selection using
=IFERROR(INDEX($B$2:$B$999,AGGREGATE(15,6,ROW($A$2:$A$999)-ROW($A$1)/($A$2:$A$999=Sheet1!$B$2),ROW()-ROW($G$1))),"")

Sheet1 C11 Data Validation again uses OFFSET to offer the Sheet4 column G entries up to the first blank entry.
=OFFSET(Sheet4!$G$2,,,COUNTIF(Sheet4!$G$2:$G$20,"> "))

RonAbba.xlsx
	A	B	C	D	E	F	G
1	Department	Service			Dept Dropdown		Item Dropdown
2	Sani	Collect			Sani		Boxes
3	Sani	Clean			Admin		Pallets
4	Sani	Wash			Transport		Loose Items
5	Sani	Deliver					People
6	Sani	Park					Empty
7	Sani	Drive					Liquids
8	Admin	Mail
9	Admin	Cargo
10	Admin	Staff
11	Transport	Boxes
12	Transport	Pallets
13	Transport	Loose Items
14	Transport	People
15	Transport	Empty
16	Transport	Liquids
Sheet4

Cell Formulas
Range	Formula
E2:E16	E2	=INDEX($A$2:$A$999,MATCH(0,INDEX(COUNTIF($E$1:$E1,$A$2:$A$999),),0))&""
G2:G16	G2	=IFERROR(INDEX($B$2:$B$999,AGGREGATE(15,6,ROW($A$2:$A$999)-ROW($A$1)/($A$2:$A$999=Sheet1!$B$2),ROW()-ROW($G$1))),"")

RonAbba.xlsx
	B	C
1	Dept Selection
2	Transport
3
4
5
6
7
8
9
10		Service Selection
11		Loose Items
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=OFFSET(Sheet4!$E$2,,,COUNTIF(Sheet4!$E$2:$E$20,"> "))
C11	List	=OFFSET(Sheet4!$G$2,,,COUNTIF(Sheet4!$G$2:$G$20,"> "))

 

[Ron Abba]

Thanks you so much for your help TS, I will try to post more info to clear things up..
Sheet 4 is where I have all my tables.

Sheet1 B2 - cell in yellow - is where I select the dept -which is out lines on sheet4 A1:F
Sheet1 C10, C11, &C12- Cells in Green - will all be drop downs to select the services needed after the Dept has been chosen.

Hope this adds more light on what Im hoping to do. Thanks so much for your time with this.

 

[Toadstool]

Ron,

Here's the modified sheets:

RonAbba2.xlsx
	A	B	C	D	E	F	G	H	I
1	Fence	Sani	Storage	Forklifts	Trailers	Admin	Services Fence		ServDrop
2							PM "A" Words here for Services Fence		PM "A" Words here for Services Trailers
3							PM "B" Words here for Services Fence		PM "B" Words here for Services Trailers
4							PM "C" Words here for Services Fence		PM "C" Words here for Services Trailers
5
6
7
8							Services Sani
9							PM "A" Words here for Services Sani
10							PM "B" Words here for Services Sani
11							PM "C" Words here for Services Sani
12
13
14
15							Services Admin
16							PM "A" Words here for Services Admin
17							PM "B" Words here for Services Admin
18							PM "C" Words here for Services Admin
19
20
21
22							Services Forklifts
23							PM "A" Words here for Services Forklifts
24							PM "B" Words here for Services Forklifts
25							PM "C" Words here for Services Forklifts
26
27
28
29							Services Storage
30							PM "A" Words here for Services Storage
31							PM "B" Words here for Services Storage
32							PM "C" Words here for Services Storage
33
34
35
36							Services Trailers
37							PM "A" Words here for Services Trailers
38							PM "B" Words here for Services Trailers
39							PM "C" Words here for Services Trailers
Sheet4

Cell Formulas
Range	Formula
I2:I7	I2	=INDEX($G$1:$G$42,MATCH("Services "&Sheet1!$B$2,$G$1:$G$42,0)+ROW(I1))&""

RonAbba2.xlsx
	B	C
1	Dept Selection
2	Trailers
3
4
5
6
7
8
9
10		PM "A" Words here for Services Trailers
11		PM "B" Words here for Services Trailers
12		PM "C" Words here for Services Trailers
13
14
15
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=Sheet4!$A$1:$F$1
C10:C15	List	=OFFSET(Sheet4!$I$2,,,COUNTIF(Sheet4!$I$2:$I$7,"> "))

 

[Ron Abba]

ToadStool, Thank you so much! I cant tell you how long I was wrestling with this, many frustrated nights.

Once again thank you for your time and knowledge, your a true resource to this community.

 

[Toadstool]

You're welcome!
Thanks for the feedback.