Help to shorten formula to fit into data validation

[ek8]

Hey all,

I am needing to shorten this formula so I can put it into a data validation drop down list.

=IF($c$11="1", 'LI'!$A$9:$A$99, IF($c$11="2", 'LI'!$b$9:$B$99, IF($c$11="4", 'LI'!$d$9:$d$99, IF($c$11="5", 'LI'!$e$9:$e$99, IF($c$11="6", 'LI'!$f$9:$f$99, IF($c$11="7", 'LI'!$g$9:$g$99, IF($c$11="8", 'LI'!$h$9:$h$99, IF($c$11="9", 'LI'!$i$9:$i$99, IF($c$11="10", 'LI'!$j$9:$j$99, IF($c$11="11", 'LI'!$k$9:$k$99, IF($c$11="13", 'LI'!$m$9:$m$99, IF($c$11="14", 'LI'!$n$9:$n$99, IF($c$11="15", 'LI'!$o$9:$o$99)))))))))))))))

Each of the parameters is referring to a column, which I need to be able to pull multiple values, based on the cell condition in C11.
The formula works fine when I had less IF conditions, but now that I have 15 separate columns to draw from, I need to revise this formula.

Does anyone have any work arounds or tips?
Thanks in advance!

 

[Toadstool]

Hi Ek8,

In its simplest form

=OFFSET(LI!$A$9,,$C$11-1,99)

If C11 isn't one of the valid options then the dropdown will not offer a list.

If the rows of the columns A, B, D, etc are contiguous data followed by unused cells then you can add this COUNTIF to limit the dropdown contents.

=OFFSET(LI!$A$9,,$C$11-1,COUNTIF(OFFSET(LI!$A$9,,$C$11-1,99),"<>"))

EK8.xlsx
	A	B	C	D	E
1
2
3
4
5
6
7
8
9
10
11			7		G 11
12
Dropdown

Cells with Data Validation
Cell	Allow	Criteria
E11	List	=OFFSET(LI!$A$9,,$C$11-1,COUNTIF(OFFSET(LI!$A$9,,$C$11-1,99),"<>"))
C11	List	1,2,4,5,6,7,8,9,10,11,13,14,15

EK8.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3
4
5
6
7
8
9	A 9	B 9	C 9	D 9	E 9	F 9	G 9	H 9	I 9	J 9	K 9	L 9	M 9	N 9	O 9
10	A 10	B 10	C 10	D 10	E 10	F 10	G 10	H 10	I 10	J 10	K 10	L 10	M 10	N 10	O 10
11	A 11	B 11	C 11	D 11	E 11	F 11	G 11	H 11	I 11	J 11	K 11	L 11	M 11	N 11	O 11
12	A 12	B 12	C 12	D 12	E 12	F 12	G 12	H 12	I 12	J 12	K 12	L 12	M 12	N 12	O 12
13	A 13	B 13	C 13	D 13	E 13	F 13		H 13	I 13	J 13	K 13	L 13	M 13	N 13	O 13
14	A 14	B 14	C 14	D 14	E 14	F 14		H 14	I 14	J 14	K 14	L 14	M 14	N 14	O 14
LI

 

[Fluff]

Another option would be

=IF($c$11<>"",INDEX('LI'!$A$9:$O$99,0,$C$11),)