Dynamic Data Validation

[m3ko]

Hello Everyone,

I have another issue I'm running into and wondering if there is an efficient solution without going into VB or using helper column(s).
Basically I want to create drop down options/lists that anyone can use to find the length.

I have simple data validation set up for dropdown list in H9 and H11 - they work ok.
Now, for H15, I want it to check for the values in both H9 and h11.

In the sheet Config Info. I am hoping to look up H9 against Column A, and then match H11's SubType between rows K3:V3 and give me the dropdown options.
For example, if H9 (size) is 2.4375 and H11 (SubType) is "H", the droplist option for H15 should be: 3.5 & 6.
If H11 (SubType) is "B", H15 should have droplist option 6.

But I don't think I can look up/do a dynamic data validation?

Any help or insight is greatly appreciated.
Thank you!

Thickness Type.xlsm
	G	H
8	Units	Imperial
9	Size	1.4375
10	Style	C_Both_Sides
11	SubType	A
12
13
14
15	Length
Tree

Cells with Data Validation
Cell	Allow	Criteria
H8:I8	List	=$T$5:$U$5
H9:I9	List	=INDIRECT(H8)
H10:I10	List	=$V$5:$X$5
H11:I12	List	=INDIRECT(H10)
H13:I13	List	=INDIRECT(H11)

Thickness Type.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
3											A	A	B	C	C	D	E	F	F	G	H	H
4	SIZE	WIDTH A	H	H	H	h				OD	LT	LT	LT	LT	LT	LT	LT	LT	LT	LT	LT	LT
5	0.375	0.0938	0.058	0.046	0.058	0.0205				2
6	0.4375	0.0938	0.057	0.047	0.057	0.021				2
7	0.5	0.125	0.075	0.060	0.075	0.034				2
8	0.562	0.125	0.074	0.061	0.074	0.035				2
9	0.625	0.1875	0.113	0.085	0.113	0.059				2
10	0.6875	0.1875	0.112	0.086	0.112	0.060				2
11	0.75	0.1875	0.110	0.087	0.110	0.062				2
12	0.8125	0.1875	0.110	0.088	0.110	0.062				2
13	0.875	0.1875	0.109	0.089	0.109	0.063				2
14	0.9375	0.25	0.147	0.113	0.147	0.088				2
15	1	0.25	0.146	0.114	0.146	0.089				2.25			2.25			2.25
16	1.0625	0.25	0.145	0.115	0.145	0.090				2.25
17	1.125	0.25	0.144	0.116	0.144	0.091				2.25
18	1.1875	0.25	0.143	0.117	0.143	0.091				2.25
19	1.25	0.25	0.143	0.118	0.143	0.092				2.25			2.25			2.25
20	1.3125	0.3125	0.180	0.143	0.180	0.117				2.5
21	1.375	0.3125	0.179	0.143	0.179	0.118				2.5
22	1.4375	0.375	0.217	0.168	0.217	0.143				3	3		2.25	3		2.25	3	3		2.25	3
23	1.5	0.375	0.216	0.169	0.216	0.144				3	3		2.25			2.25					3
24	1.5625	0.375	0.215	0.170	0.215	0.145				3
25	1.625	0.375	0.214	0.171	0.214	0.145				3
26	1.6875	0.375	0.213	0.172	0.213	0.146				3
27	1.75	0.375	0.213	0.172	0.213	0.147				3.5	3		2.25			2.25					3
28	1.8125	0.5	0.289	0.221	0.289	0.195				3.5
29	1.875	0.5	0.288	0.222	0.288	0.196				3.5
30	1.9375	0.5	0.287	0.223	0.287	0.197				4	3.5		2.25	3.5		2.25	3.5	3.5		2.25	3.5
31	2	0.5	0.286	0.224	0.286	0.198				4	3.5		2.25			2.25					3.5
32	2.0625	0.5	0.285	0.225	0.285	0.199				4
33	2.125	0.5	0.284	0.226	0.284	0.200				4
34	2.1875	0.5	0.284	0.226	0.284	0.201				4	3.5		2.25	3.5		2.25		3.5		2.25	3.5
35	2.25	0.5	0.283	0.227	0.283	0.202				4
36	2.3125	0.625	0.360	0.275	0.360	0.250				4.5
37	2.375	0.625	0.359	0.276	0.359	0.251				4.5
38	2.4375	0.625	0.358	0.277	0.358	0.252				5	3.5	6	2.25	3.5	6	2.25	6	3.5	6	2.25	3.5	6
39	2.5	0.625	0.357	0.278	0.357	0.253				5			2.25			2.25
Config Info

Cell Formulas
Range	Formula
C5:C39	C5	=((POWER(B5,2))/(4*A5))+(B5/2)+0.005
D5:D39	D5	=(B5/2)-((POWER(B5,2))/(4*A5))+0.005
E5:E39	E5	=((POWER(B5,2))/(4*A5))+(B5/2)+0.005
F5:F39	F5	=(B5/2)-((POWER(B5,2))/(4*A5))-0.0205

 

[Peter_SSs]

I think you would need helper cells somewhere. I have used J15 and below but it could be anywhere, including on the Config Info sheet.

m3ko_1.xlsm
	G	H	I	J
8	Units	Imperial
9	Size	2.4375
10	Style	C_Both_Sides
11	SubType	H
12
13
14
15	Length			3.5
16				6
17
Tree

Cell Formulas
Range	Formula
J15:J16	J15	=LET(r,INDEX('Config Info'!$K$6:$V$40,MATCH(H9,'Config Info'!$A$6:$A$40,0),0),TRANSPOSE(FILTER(r,('Config Info'!$K$4:$V$4=H11)*(r<>""),"")))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
H15	List	=$J$15#