Hi

I have the following formula. I use this formula to collect different
values for different months (i.e.) if (month)=4,
AG160+AH160+AI160+AJ160, if (month)=5, AG160+AH160+AI160+AJ160+AK160
and so on .....

The actual formula is

=IF($J$1=4,AG160+AH160+AI160+AJ160,IF($J$1=6,AG160 +AH160+AI160+AJ160+AK160+AL160,IF($J$1=7,SUM(AG160 :AM160),IF($J$1=8,AG160+AH160+AI160+AJ160+AK160+AL 160+AM160+AN160,IF($J$1=9,AG160+AH160+AI160+AJ160+ AK160+AL160+AM160+AN160+AO160,IF($J$1=10,AG160+AH1 60+AI160+AJ160+AK160+AL160+AM160+AN160+AO160+AP160 ,IF($J$1=11,AG160+AH160+AI160+AJ160+AK160+AL160+AM 160+AN160+AO160+AP160+AQ160,IF($J$1=12,AG160+AH160 +AI160+AJ160+AK160+AL160+AM160+AN160+AO160+AP160+A Q160+AR160,0))))))))

However I am stuck now with the max limit of 7 nested loops - hence
manually changing the formula from month to month .....

is there a way I can do this once and for all (i.e.) without having to
change month to month ?


Thanks

Subu

try this idea
=SUM(OFFSET(ag160,0,0,1,j1))

--
Don Guillett
SalesAid Software

"Subu" wrote in message
oups.com...
Hi

I have the following formula. I use this formula to collect different
values for different months (i.e.) if (month)=4,
AG160+AH160+AI160+AJ160, if (month)=5, AG160+AH160+AI160+AJ160+AK160
and so on .....

The actual formula is


=IF($J$1=4,AG160+AH160+AI160+AJ160,IF($J$1=6,AG160 +AH160+AI160+AJ160+AK160+A
L160,IF($J$1=7,SUM(AG160:AM160),IF($J$1=8,AG160+AH 160+AI160+AJ160+AK160+AL16
0+AM160+AN160,IF($J$1=9,AG160+AH160+AI160+AJ160+AK 160+AL160+AM160+AN160+AO16
0,IF($J$1=10,AG160+AH160+AI160+AJ160+AK160+AL160+A M160+AN160+AO160+AP160,IF(
$J$1=11,AG160+AH160+AI160+AJ160+AK160+AL160+AM160+ AN160+AO160+AP160+AQ160,IF
($J$1=12,AG160+AH160+AI160+AJ160+AK160+AL160+AM160 +AN160+AO160+AP160+AQ160+A
R160,0))))))))

However I am stuck now with the max limit of 7 nested loops - hence
manually changing the formula from month to month .....

is there a way I can do this once and for all (i.e.) without having to
change month to month ?


Thanks

Subu

Hi Subu

In this situation you can use this formula:

=SUM(OFFSET($AG$160,,,,$J$1))

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Subu" skrev i en meddelelse
oups.com...
Hi

I have the following formula. I use this formula to collect different
values for different months (i.e.) if (month)=4,
AG160+AH160+AI160+AJ160, if (month)=5, AG160+AH160+AI160+AJ160+AK160
and so on .....

The actual formula is

=IF($J$1=4,AG160+AH160+AI160+AJ160,IF($J$1=6,AG160 +AH160+AI160+AJ160+AK160+AL160,IF($J$1=7,SUM(AG160 :AM160),IF($J$1=8,AG160+AH160+AI160+AJ160+AK160+AL 160+AM160+AN160,IF($J$1=9,AG160+AH160+AI160+AJ160+ AK160+AL160+AM160+AN160+AO160,IF($J$1=10,AG160+AH1 60+AI160+AJ160+AK160+AL160+AM160+AN160+AO160+AP160 ,IF($J$1=11,AG160+AH160+AI160+AJ160+AK160+AL160+AM 160+AN160+AO160+AP160+AQ160,IF($J$1=12,AG160+AH160 +AI160+AJ160+AK160+AL160+AM160+AN160+AO160+AP160+A Q160+AR160,0))))))))

However I am stuck now with the max limit of 7 nested loops - hence
manually changing the formula from month to month .....

is there a way I can do this once and for all (i.e.) without having to
change month to month ?


Thanks

Subu