Dependent Drop Down list

[abdulads]

Hi All, Wanted to have dependent drop down list to be derived from the 1st table in drop down list as in second table.

Department	Process	Staff1	Level	Description	Remarks
Advisory	Sales	David	Sales Manager	Take Care of Sales	Excellent
Advisory	Operation	Johnson	Manager	Manages Operation	N.A
Advisory	Marketing	Abdul	Executive	Execute Marketing	N.A
Advisory	Logistics	David	Sales Manager	Manages Logistics	Need to Improve
Audit	Sales	David	Sales Manager	Take Care of Sales	Excellent
Audit	Operation	Johnson	Manager	Manages Operation	N.A
Audit	Marketing	Abdul	Executive	Execute Marketing	N.A
Audit	Logistics	David	Sales Manager	Manages Logistics	Need to Improve

Department	Drop down
Process	Drop down
Staff1	Drop down
Level	Drop down
Description	Drop down
Remarks	Drop down

 

[Kerryx]

Use Data Validation

Book1
	A	B	C	D	E	F
1	Department	Process	Staff1	Level	Description	Remarks
2	Advisory	Sales	David	Sales Manager	Take Care of Sales	Excellent
3	Advisory	Operation	Johnson	Manager	Manages Operation	N.A
4	Advisory	Marketing	Abdul	Executive	Execute Marketing	N.A
5	Advisory	Logistics	David	Sales Manager	Manages Logistics	Need to Improve
6	Audit	Sales	David	Sales Manager	Take Care of Sales	Excellent
7	Audit	Operation	Johnson	Manager	Manages Operation	N.A
8	Audit	Marketing	Abdul	Executive	Execute Marketing	N.A
9	Audit	Logistics	David	Sales Manager	Manages Logistics	Need to Improve
10
11	Department
12	Process
13	Staff1
14	Level
15	Description
16	Remarks
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B11	List	=$A$2:$A$9
B12	List	=$B$2:$B$9
B13	List	=$C$2:$C$9
B14	List	=$D$2:$D$9
B15	List	=$E$2:$E$9
B16	List	=$F$2:$F$9

 

[abdulads]

Thank you for your reply but i wanted dependent Drop down.

Example if i select Audit in cell B11 it should show only once in B12 Sales, operation ,marketing , logistic, and when i select Sales in B12 then it should only show in b13 as david and in B14 is should be Sales Manager so on so forth.

 

[Kerryx]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Department	Process	Staff1	Level	Description	Remarks				Helper Table
2	Advisory	Sales	David	Sales Manager	Take Care of Sales	Excellent				Department	Process	Staff1
3	Advisory	Operation	Johnson	Manager	Manages Operation	N.A				Advisory	Sales2	Johnson
4	Advisory	Marketing	Abdul	Executive	Execute Marketing	N.A				Audit	OP2
5	Advisory	Logistics	David	Sales Manager	Manages Logistics	Need to Improve					Marketing
6	Audit	Sales2	David	Sales Manager	Take Care of Sales	Excellent					Logistics
7	Audit	OP2	Johnson	Manager	Manages Operation	N.A
8	Audit	Marketing	Paul	Executive	Execute Marketing	N.A
9	Audit	Logistics	David	Sales Manager	Manages Logistics	Need to Improve
10
11	Department	Audit
12	Process	OP2
13	Staff1	Johnson
14	Level	Manager
15	Description	Manages Operation
16	Remarks	N.A
Sheet1

Cell Formulas
Range	Formula
J3:J4	J3	=UNIQUE(A2:A9,FALSE)
K3:K6	K3	=UNIQUE(FILTER($B$2:$B$9,$A$2:$A$9=$B$11))
L3	L3	=FILTER($C$2:$C$9,($A$2:$A$9=$B$11)*($B$2:$B$9=$B$12))
B14	B14	=IFERROR(FILTER(D2:D9,(A2:A9=$B$11)*(B2:B9=$B$12)*(C2:C9=$B$13)),"")
B15	B15	=IFERROR(FILTER(E2:E9,(A2:A9=$B$11)*(B2:B9=$B$12)*(C2:C9=$B$13)*(D2:D9=$B$14)),"")
B16	B16	=IFERROR(FILTER(F2:F9,(A2:A9=$B$11)*(B2:B9=$B$12)*(C2:C9=$B$13)*(D2:D9=$B$14)*(E2:E9=B15)),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B11	List	=$J$3#
B12	List	=$K$3#
B13	List	=$L$3#