Data Validation Question. Can You Have One Dropdown That Controls Multiple Dependent Dropdowns

[droberts7510]

I have a spreadsheet that we are trying to build dependent dropdowns for several values.
Column A is the 34 Organizations. Each of those 34 Organizations has a unique list of Owners, Departments, Areas, and Accounts. I have limited the the sample file to 10 organizations.
The desire is to be able to select the Organization from the dropdown and it limits the values in the Owners, Departments, Areas, Accounts and Account Description.

I have tried to do it with various methods of data validation but have been unsuccessful.

Forum Test File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Organization	Owning Department	Area	Account		ORGANIZATION	COC_DEPT	COE_DEPT	COH_DEPT	COC_AREA	COE_AREA	COH_AREA	COC_ACT	COE_ACT	COH_ACT
2	COC	COC_DEPT1				COC	COC_DEPT1	COE_DEPT1	COH_DEPT1	COC_AREA1	COE_AREA1	COH_AREA1	1350022009	1350022008	1310022001
3	COE	COE_DEPT1				COE	COC_DEPT2	COE_DEPT2	COH_DEPT2	COC_AREA2	COE_AREA2	COH_AREA2	1350022005	1350022001	0080307143
4	COH	COH_DEPT1				COH	COC_DEPT3	COE_DEPT3	COH_DEPT3	COC_AREA3	COE_AREA3	COH_AREA3	1310121005	1350020054	0040107097
5	COC	COC_DEPT2					COC_DEPT4	COE_DEPT4	COH_DEPT4	COC_AREA4	COE_AREA4	COH_AREA4	1310022010	1310074712	0000000066
6	COE	COE_DEPT2					COC_DEPT5	COE_DEPT5	COH_DEPT5	COC_AREA5	COE_AREA5	COH_AREA5	1310020702	1310074710	0000000065
7	COH	COH_DEPT2					COC_DEPT6	COE_DEPT6	COH_DEPT6	COC_AREA6	COE_AREA6	COH_AREA6	1310020701	1310022001	0000000064
8	COC	COC_DEPT3					COC_DEPT7	COE_DEPT7	COH_DEPT7	COC_AREA7	COE_AREA7	COH_AREA7	1310020700	1310020600	0000000021
9	COE	COE_DEPT3					COC_DEPT8	COE_DEPT8	COH_DEPT8	COC_AREA8	COE_AREA8	COH_AREA8	1310020006	1310020054
10	COH	COH_DEPT3					COC_DEPT9	COE_DEPT9	COH_DEPT9	COC_AREA9	COE_AREA9	COH_AREA9	1310020005	1310020053
11							COC_DEPT10	COE_DEPT10	COH_DEPT10	COC_AREA10	COE_AREA10	COH_AREA10		1310020052
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2:A10	List	=ORGANIZATION
B2:B10	List	=INDIRECT(A2)
C2:C10	List	=INDIRECT(SUBSTITUTE(A2,"_AREA",""))
D2:D10	List	=INDIRECT(SUBSTITUTE(A2,"_ACT",""))

 

[kevin9999]

One possibility, although I'm hoping a more elegant way comes along.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Organization	Owning Department	Area	Account		ORGANIZATION	COC_DEPT	COE_DEPT	COH_DEPT	COC_AREA	COE_AREA	COH_AREA	COC_ACT	COE_ACT	COH_ACT
2	COC	COC_DEPT1	COC_AREA4	1350022005		COC	COC_DEPT1	COE_DEPT1	COH_DEPT1	COC_AREA1	COE_AREA1	COH_AREA1	1350022009	1350022008	1310022001
3	COE	COE_DEPT1				COE	COC_DEPT2	COE_DEPT2	COH_DEPT2	COC_AREA2	COE_AREA2	COH_AREA2	1350022005	1350022001	80307143
4	COH	COH_DEPT1	COH_AREA3			COH	COC_DEPT3	COE_DEPT3	COH_DEPT3	COC_AREA3	COE_AREA3	COH_AREA3	1310121005	1350020054	40107097
5							COC_DEPT4	COE_DEPT4	COH_DEPT4	COC_AREA4	COE_AREA4	COH_AREA4	1310022010	1310074712	66
6	COE		COE_AREA2	1310020052			COC_DEPT5	COE_DEPT5	COH_DEPT5	COC_AREA5	COE_AREA5	COH_AREA5	1310020702	1310074710	65
7	COH		COH_AREA2				COC_DEPT6	COE_DEPT6	COH_DEPT6	COC_AREA6	COE_AREA6	COH_AREA6	1310020701	1310022001	64
8	COC	COC_DEPT3	COC_AREA3				COC_DEPT7	COE_DEPT7	COH_DEPT7	COC_AREA7	COE_AREA7	COH_AREA7	1310020700	1310020600	21
9	COE	COE_DEPT3		1310020600			COC_DEPT8	COE_DEPT8	COH_DEPT8	COC_AREA8	COE_AREA8	COH_AREA8	1310020006	1310020054
10	COH	COH_DEPT3		66			COC_DEPT9	COE_DEPT9	COH_DEPT9	COC_AREA9	COE_AREA9	COH_AREA9	1310020005	1310020053
11							COC_DEPT10	COE_DEPT10	COH_DEPT10	COC_AREA10	COE_AREA10	COH_AREA10		1310020052
12
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2:A10	List	=$F$2:$F$4
B2:B10	List	=OFFSET($G$1,1,MATCH($A2&"_DEPT",$G$1:$I$1,0)-1,10,1)
C2:C10	List	=OFFSET($J$1,1,MATCH($A2&"_AREA",$J$1:$L$1,0)-1,10,1)
D2:D10	List	=OFFSET($M$1,1,MATCH($A2&"_ACT",$M$1:$O$1,0)-1,10,1)

 

[Georgiboy]

If you convert your data lists (columns F to O) into table objects, then name the tables as the header names you already have in place then you can use the table objects in the data validation as below:

Range A2:A10:

=INDIRECT("ORGANIZATION")

Range B2:B10:

=INDIRECT(A2&"_DEPT")

Range C2:C10:

=INDIRECT(A2&"_AREA")

Range D2:D10:

=INDIRECT(A2&"_ACT")

This way when you add items to your tables, your data validation will update automatically.

The only thing that will stop this working is if this is a shared file as you can't use tables in a shared file.

 

[Georgiboy]

You may need to remove your current named ranges from the name manager, as it will conflict with the naming of the tables. Sometimes I end up just creating a new file with no named ranges as I quite often get errors when trying to name a table with the same name as a named range even if the named range has been deleted (not sure why)

 

[droberts7510]

Thank you Georgiboy. That worked perfectly.

 

[Georgiboy]

You're welcome, thanks for the feedback.