Dynamic sum

[opislak]

Hello all,

I would like to make a Sum, based on a variable range of cells. Now I use an INDIRECT, but when I want to copy this formula to the cells on the right, the references stay the same, as they are considered as text.
Is there a possibility for automatically turning the Y into Z as I copy the formula from cell Y2 to cell Z2?
For your information: in cell $A$2 I store the number of lines of the used range. I fill cell A2 by means of a macro.

=SUM(INDIRECT("Y3:Y"&$A$2))

Many thanks!!
Patrick.

 

[jasonb75]

It's easier if you don't use indirect.

=SUM(Y3:INDEX(Y:Y,$A$2))

 

[opislak]

It's easier if you don't use indirect.

=SUM(Y3:INDEX(Y:Y,$A$2))

Works like a charm. Many thanks !!!
Have a nice day.
Patrick.