Limit to the number of items in a cell
 

Is there a limit to the number of parentheses you can put into one cell in
excell's spreadsheet?
For instance, I try to enter:

=if(d4=1,offset(data!c24,0,0),if(d4=2,offset(data! d24,0,0),if(d4=3,offset(data!e24,0,0),
if(d4=4,offset(data!f24,0,0),if(d4=5,offset(data!c 43,0,0),if(d4=6,offset(data!d43,0,0),if(d4=7,offse t(data!e43,0,0),if(d4=8,offset(data!f43,0,0),if(D4 =9,Offset(data!c62,0,0),if(d4=10,offset(data!d62,0 ,0),if(d4=11,Offset(data!e62,0,0),if(d4=12,offset( data!f62,0,0),""))))))))))))

but excel won't let the function through, saying that there is an "error"
after the d4=7 part. If you know of any such limit, or a better and more
efficient way to go about what I'm doing, it would be much appreciated.
Thanks in advance.

You hit the IF limit (see the Help file).

This should do what you want:

=OFFSET(INDIRECT("data!"&CHOOSE(D4,"C24","D24","E2 4","F24","C43","D43","E43"
,"F43","C62","D62","E62","F62")),0,0)

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Jim_Bowie" wrote in message
...
Is there a limit to the number of parentheses you can put into one cell in
excell's spreadsheet?
For instance, I try to enter:


=if(d4=1,offset(data!c24,0,0),if(d4=2,offset(data! d24,0,0),if(d4=3,offset(da
ta!e24,0,0),

if(d4=4,offset(data!f24,0,0),if(d4=5,offset(data!c 43,0,0),if(d4=6,offset(dat
a!d43,0,0),if(d4=7,offset(data!e43,0,0),if(d4=8,of fset(data!f43,0,0),if(D4=9
,Offset(data!c62,0,0),if(d4=10,offset(data!d62,0,0 ),if(d4=11,Offset(data!e62
,0,0),if(d4=12,offset(data!f62,0,0),""))))))))))))

but excel won't let the function through, saying that there is an "error"
after the d4=7 part. If you know of any such limit, or a better and more
efficient way to go about what I'm doing, it would be much appreciated.
Thanks in advance.

Thanks once again

"Andy Wiggins" wrote:

You hit the IF limit (see the Help file).

This should do what you want:

=OFFSET(INDIRECT("data!"&CHOOSE(D4,"C24","D24","E2 4","F24","C43","D43","E43"
,"F43","C62","D62","E62","F62")),0,0)

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Jim_Bowie" wrote in message
...
Is there a limit to the number of parentheses you can put into one cell in
excell's spreadsheet?
For instance, I try to enter:


=if(d4=1,offset(data!c24,0,0),if(d4=2,offset(data! d24,0,0),if(d4=3,offset(da
ta!e24,0,0),

if(d4=4,offset(data!f24,0,0),if(d4=5,offset(data!c 43,0,0),if(d4=6,offset(dat
a!d43,0,0),if(d4=7,offset(data!e43,0,0),if(d4=8,of fset(data!f43,0,0),if(D4=9
,Offset(data!c62,0,0),if(d4=10,offset(data!d62,0,0 ),if(d4=11,Offset(data!e62
,0,0),if(d4=12,offset(data!f62,0,0),""))))))))))))

but excel won't let the function through, saying that there is an "error"
after the d4=7 part. If you know of any such limit, or a better and more
efficient way to go about what I'm doing, it would be much appreciated.
Thanks in advance.