Data validation depending on previous data validation

[DavidPonnet]

Hello folks,

I am having a hard time trying to figure out following.

I have a few typologies of cases (case1, case2, case3, etc)

Every case has its own restrictions to opening mechanisms & lighting & many more options.

unlike this example --> How to make Excel Data Validation Dependent Lists where a fruit or vegetable has only 1 extra option, i need to be able to select many different options per case type (fe. opening mechanism or lighting)

I have managed to get a solution with the "IF" function inside the data validation, but this only works really well if you only have 2 options, given the fact I have multi case types, I would need to use multiple IF functions, and it would become really messy. (see cells in yellow)

The range in RED is a test, and I was looking for a alternative solution where the list changes depending on the choice blablabla

All kind of difficult to explain like this I hope I did a good job. All help is more then welcome because I have been cracking my brain on this for a few hours now.

Testing options.xlsx
	A	B	C	D
1	Type	Case2
2	Opening
3	Lighting
4
5
6	Typology	OPENING	LIGHTING	Case2
7	Case1			Door 1
8	Case2			Door 2
9	Case3			Door 3
10	Case4			Door 4
11	Case5			Door 5
12	Case6			Door 6
13	Case7			Door 7
14	Case8			Door 8
15	Case9			Door 9
16	Case10			Door 10
17	Case11			0
18				0
19
20
21	OPENING
22	Case1	Case2	Case3	Case4
23	Door 1	Door 1	Door 1	N.A.
24	Door 2	Door 2	Door 2
25	Door 3	Door 3	Door 3
26	Door 4	Door 4	Door 4
27	Door 5	Door 5	Door 5
28	Door 6	Door 6	Door 6
29	Door 7	Door 7	Door 7
30	Door 8	Door 8
31	Door 9	Door 9
32	Door 10	Door 10
33	Door 11
34	Door 12
35
36
37
38	Lighting
39	Case1	Case2	Case3	Case4
40	Lights 1	Lights 1	Lights 1	N.A.
41	Lights 2	Lights 2	Lights 2
42	Lights 3		Lights 3
43	Lights 4		Lights 4
44			Lights 5
45			Lights 6
46			Lights 7
47			Lights 8
48			Lights 9
Sheet2

Cell Formulas
Range	Formula
D6	D6	=B1
D7:D18	D7	=IF(D6=A7,OPENING_MECH[Case1],OPENING_MECH[Case2])
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=$A$7:$A$18
B2	List	=IF($B$1=$A$22;$A$23:$A$34;$B$23:$B$34)

 

[mamady]

Hi David,

I think the following video tutorial will address your issue

Let me know if I can help you with the formulas if you have a sample document that you can share

Best Regards
M. Yusuf

 

[DavidPonnet]

Yes, thank you, I have found a solution similar to that in the meantime.