How to exceed character limit for Data Validation List

[ValiantGames]

Hi all! I am running multiple offset functions with nested ifs to return a drop down list dependant on 3 variables (A,B,C). Is there a way I can avoid the character limit for data validation? I don't know how I can simplify this formula.

Variabe A = Ability Level (0,1,2,3) (Purple) B3
Variable B= Range (High, Medium, Low) (White) B19
Variable C = AOE (High, Medium Low) (light Blue) B18


=IF(B18="High",OFFSET((IF(B3=0,$AB$119,IF(B3=1,$S$119,IF(B3=2,$J$119,IF(B3=3,$A$119))))),1,MATCH($B$19,$A$119:$C$119,0)-1,COUNTA(OFFSET((IF(B3=0,$AB$119,IF(B3=1,$S$119,IF(B3=2,$J$119,IF(B3=3,$A$119))))),1,MATCH($B$19,$A$119:$C$119,0)-1,100,1))),

IF(B18="Medium",OFFSET((IF(B3=0,$AE$119,IF(B3=1,$V$119,IF(B3=2,$M$119,IF(B3=3,$D$119))))),1,MATCH($B$19,$A$119:$C$119,0)-1,COUNTA(OFFSET((IF(B3=0,$AE$119,IF(B3=1,$V$119,IF(B3=2,$M$119,IF(B3=3,$D$119))))),1,MATCH($B$19,$A$119:$C$119,0)-1,100,1))),

IF(B18="Low",OFFSET((IF(B3=0,$AH$119,IF(B3=1,$Y$119,IF(B3=2,$P$119,IF(B3=3,$G$119))))),1,MATCH($B$19,$A$119:$C$119,0)-1,COUNTA(OFFSET((IF(B3=0,$AH$119,IF(B3=1,$Y$119,IF(B3=2,$P$119,IF(B3=3,$G$119))))),1,MATCH($B$19,$A$119:$C$119,0)-1,100,1))),0)))

 

[Peter_SSs]

Is there a way I can avoid the character limit for data validation?

If I have understood correctly and the COUNTA part is counting how many values appear in the relevant column from row 120 down, then I would suggest this.

Add these formulas across row 116 so that the counts are readily available (I just have dummy data from row 120 down)

ValiantGames.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
116	4	5	7	2	3	4	7	4	4	7	7	7	7	7	6	4	7	5	7	7	7	7	7	7	7	7	7	3	4	5	6	7	7	4	3	7
117	Ability level 3									Ability level 2									Ability level 1									Ability level 0
118	High			Medium			Low			High			Medium			Low			High			Medium			Low			High			Medium			Low
119	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High
120	A120	B120	C120	D120	E120	F120	G120	H120	I120	J120	K120	L120	M120	N120	O120	P120	Q120	R120	S120	T120	U120	V120	W120	X120	Y120	Z120	AA120	AB120	AC120	AD120	AE120	AF120	AG120	AH120	AI120	AJ120
121	A121	B121	C121	D121	E121	F121	G121	H121	I121	J121	K121	L121	M121	N121	O121	P121	Q219	R121	S121	T121	U121	V121	W121	X121	Y121	Z121	AA121	AB121	AC121	AD121	AE121	AF121	AG121	AH121	AI121	AJ121
122	A122	B122	C122		E122	F122	G122	H122	I122	J122	K122	L122	M122	N122	O122	P122	Q319	R122	S122	T122	U122	V122	W122	X122	Y122	Z122	AA122	AB122	AC122	AD122	AE122	AF122	AG122	AH122	AI122	AJ122
123	A123	B123	C123			F123	G123	H123	I123	J123	K123	L123	M123	N123	O123	P123	Q419	R123	S123	T123	U123	V123	W123	X123	Y123	Z123	AA123		AC123	AD123	AE123	AF123	AG123	AH123		AJ123
124		B124	C124				G124			J124	K124	L124	M124	N124	O124		Q120	R124	S124	T124	U124	V124	W124	X124	Y124	Z124	AA124			AD124	AE124	AF124	AG124			AJ124
125			C125				G125			J125	K125	L125	M125	N125	O125		Q220		S125	T125	U125	V125	W125	X125	Y125	Z125	AA125				AE125	AF125	AG125			AJ125
126			C126				G126			J126	K126	L126	M126	N126			Q320		S126	T126	U126	V126	W126	X126	Y126	Z126	AA126					AF126	AG126			AJ126
Sheet1

Cell Formulas
Range	Formula
A116:AJ116	A116	=COUNTA(A120:A220)

Then use this as your Data Validation formula.

=OFFSET($A$120,0,(3-$B$3)*9+(3-MATCH($B$19,$A$119:$C$119,0))*3+MATCH($B$18,$A$119:$C$119,0)-1,OFFSET($A$120,-4,(3-$B$3)*9+(3-MATCH($B$19,$A$119:$C$119,0))*3+MATCH($B$18,$A$119:$C$119,0)-1),1)

 

[Peter_SSs]

If the calculations in that previous method are a bit complex, here is another approach that could be taken and may be more intuitive?

These formulas copied across rows 115 and 166 (may have to adjust this location or else what is already in row 115)

ValiantGames.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
115	3HL	3HM	3HH	3ML	3MM	3MH	3LL	3LM	3LH	2HL	2HM	2HH	2ML	2MM	2MH	2LL	2LM	2LH	1HL	1HM	1HH	1ML	1MM	1MH	1LL	1LM	1LH	0HL	0HM	0HH	0ML	0MM	0MH	0LL	0LM	0LH
116	A120:A123	B120:B124	C120:C126	D120:D121	E120:E122	F120:F123	G120:G126	H120:H123	I120:I123	J120:J126	K120:K126	L120:L126	M120:M126	N120:N126	O120:O125	P120:P123	Q120:Q126	R120:R124	S120:S126	T120:T126	U120:U126	V120:V126	W120:W126	X120:X126	Y120:Y126	Z120:Z126	AA120:AA126	AB120:AB122	AC120:AC123	AD120:AD124	AE120:AE125	AF120:AF126	AG120:AG126	AH120:AH123	AI120:AI122	AJ120:AJ126
117	Ability level 3									Ability level 2									Ability level 1									Ability level 0
118	High			Medium			Low			High			Medium			Low			High			Medium			Low			High			Medium			Low
119	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High
120	A120	B120	C120	D120	E120	F120	G120	H120	I120	J120	K120	L120	M120	N120	O120	P120	Q120	R120	S120	T120	U120	V120	W120	X120	Y120	Z120	AA120	AB120	AC120	AD120	AE120	AF120	AG120	AH120	AI120	AJ120
121	A121	B121	C121	D121	E121	F121	G121	H121	I121	J121	K121	L121	M121	N121	O121	P121	Q219	R121	S121	T121	U121	V121	W121	X121	Y121	Z121	AA121	AB121	AC121	AD121	AE121	AF121	AG121	AH121	AI121	AJ121
122	A122	B122	C122		E122	F122	G122	H122	I122	J122	K122	L122	M122	N122	O122	P122	Q319	R122	S122	T122	U122	V122	W122	X122	Y122	Z122	AA122	AB122	AC122	AD122	AE122	AF122	AG122	AH122	AI122	AJ122
123	A123	B123	C123			F123	G123	H123	I123	J123	K123	L123	M123	N123	O123	P123	Q419	R123	S123	T123	U123	V123	W123	X123	Y123	Z123	AA123		AC123	AD123	AE123	AF123	AG123	AH123		AJ123
124		B124	C124				G124			J124	K124	L124	M124	N124	O124		Q120	R124	S124	T124	U124	V124	W124	X124	Y124	Z124	AA124			AD124	AE124	AF124	AG124			AJ124
125			C125				G125			J125	K125	L125	M125	N125	O125		Q220		S125	T125	U125	V125	W125	X125	Y125	Z125	AA125				AE125	AF125	AG125			AJ125
126			C126				G126			J126	K126	L126	M126	N126			Q320		S126	T126	U126	V126	W126	X126	Y126	Z126	AA126					AF126	AG126			AJ126
Sheet1 (2)

Cell Formulas
Range	Formula
A115:AJ115	A115	=RIGHT(LOOKUP("zzz",$A117:A117),1)&LEFT(LOOKUP("zzz",$A118:A118),1)&LEFT(A119,1)
A116:AJ116	A116	=ADDRESS(ROW(A120),COLUMN(),4)&":"&ADDRESS(ROW(A120)+COUNTA(A120:A220)-1,COLUMN(),4)

Then use this as your Data Validation formula.

=INDIRECT(XLOOKUP($B$3&LEFT($B$19,1)&LEFT($B$18,1),$A$115:$AJ$115,$A$116:$AJ$116))

 

[ValiantGames]

If I have understood correctly and the COUNTA part is counting how many values appear in the relevant column from row 120 down, then I would suggest this.

Add these formulas across row 116 so that the counts are readily available (I just have dummy data from row 120 down)

ValiantGames.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
116	4	5	7	2	3	4	7	4	4	7	7	7	7	7	6	4	7	5	7	7	7	7	7	7	7	7	7	3	4	5	6	7	7	4	3	7
117	Ability level 3									Ability level 2									Ability level 1									Ability level 0
118	High			Medium			Low			High			Medium			Low			High			Medium			Low			High			Medium			Low
119	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High	Low	Medium	High
120	A120	B120	C120	D120	E120	F120	G120	H120	I120	J120	K120	L120	M120	N120	O120	P120	Q120	R120	S120	T120	U120	V120	W120	X120	Y120	Z120	AA120	AB120	AC120	AD120	AE120	AF120	AG120	AH120	AI120	AJ120
121	A121	B121	C121	D121	E121	F121	G121	H121	I121	J121	K121	L121	M121	N121	O121	P121	Q219	R121	S121	T121	U121	V121	W121	X121	Y121	Z121	AA121	AB121	AC121	AD121	AE121	AF121	AG121	AH121	AI121	AJ121
122	A122	B122	C122		E122	F122	G122	H122	I122	J122	K122	L122	M122	N122	O122	P122	Q319	R122	S122	T122	U122	V122	W122	X122	Y122	Z122	AA122	AB122	AC122	AD122	AE122	AF122	AG122	AH122	AI122	AJ122
123	A123	B123	C123			F123	G123	H123	I123	J123	K123	L123	M123	N123	O123	P123	Q419	R123	S123	T123	U123	V123	W123	X123	Y123	Z123	AA123		AC123	AD123	AE123	AF123	AG123	AH123		AJ123
124		B124	C124				G124			J124	K124	L124	M124	N124	O124		Q120	R124	S124	T124	U124	V124	W124	X124	Y124	Z124	AA124			AD124	AE124	AF124	AG124			AJ124
125			C125				G125			J125	K125	L125	M125	N125	O125		Q220		S125	T125	U125	V125	W125	X125	Y125	Z125	AA125				AE125	AF125	AG125			AJ125
126			C126				G126			J126	K126	L126	M126	N126			Q320		S126	T126	U126	V126	W126	X126	Y126	Z126	AA126					AF126	AG126			AJ126
Sheet1

Cell Formulas
Range	Formula
A116:AJ116	A116	=COUNTA(A120:A220)

Then use this as your Data Validation formula.

=OFFSET($A$120,0,(3-$B$3)*9+(3-MATCH($B$19,$A$119:$C$119,0))*3+MATCH($B$18,$A$119:$C$119,0)-1,OFFSET($A$120,-4,(3-$B$3)*9+(3-MATCH($B$19,$A$119:$C$119,0))*3+MATCH($B$18,$A$119:$C$119,0)-1),1)

Bro that is tight!! thank you! I need to study the match function more

 

[ValiantGames]

Thank you so much Peter

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

You have quoted post 2 & apparently commented on that but marked post 3 so, as a matter of interest, I was wondering which one you ended up using?