MrExcel Publishing
Your One Stop for Excel Tips & Solutions

nesting limitations

Posted by Philip Pavlich on October 30, 2000 5:57 PM

I want to add certain cells if certain conditions are met in another cell. The conditions are: if cell a1= 1, then sum cells J21:K21, if cell a1= 2, then sum cells J21:L21; ... if cell a1 = 12, then sum cells J21:V21; if cell a1=anything else, then return "HELP".
I tried nesting this but I think I went over the 7 level limit. At least I think thats the problem. Any ideas? Thanks.

Posted by Ivan Moala on October 30, 2000 11:47 PM

You haven't gone past the 7 limitation, it may
well be the format you have it in;
Try something like this ??



Posted by Philip on October 31, 2000 6:50 AM

Thanks Ivan. I tried that, but when I get all 12 arguments in there, I get an error message. However, I tried splitting it up and it worked. I can place the second part of the formula in another cell and hide it. Thanks again for your help. Philip

Posted by Tim Francis-Wright on October 31, 2000 9:18 AM

You might also try using the CHOOSE function:
The only problem is that it expects A1 to
have a integer value from 1 to the number
of possible answers. So, you might use

=IF(or(a1<1,a1>12,a1<>INT(a1)),"HELP",SUM(CHOOSE(A1,J21:K21,J21:L21,J21:M21 [etc] , J21:V21)))