absolute row numbers in a function

[englishrob]

cell A1 contains a function SUM(A$2:A$11).

each time I add data I insert a row above A2 before entering it into new cell A$2, as i want the function to sum only 10 most recently inserted cells. Problem is Excel updates the formula to SUM(A$3:A$12). (this is the simplified version of the problem - there are other functions as well), so I have to amend the formulae after every Insert.

I thought by using absolute row number this wouldn't happen, i.e. it would always refer to A$2:A$11, without needing to amend.

is what I want achievable ?

Best regards

 

[shaowu459]

try this:

=SUM(INDIRECT("A2:A11"))

 

[englishrob]

cell A1 contains a function SUM(A$2:A$11).

each time I add data I insert a row above A2 before entering it into new cell A$2, as i want the function to sum only 10 most recently inserted cells. Problem is Excel updates the formula to SUM(A$3:A$12). (this is the simplified version of the problem - there are other functions as well), so I have to amend the formulae after every Insert.

I thought by using absolute row number this wouldn't happen, i.e. it would always refer to A$2:A$11, without needing to amend.

is what I want achievable ?

Best regards

Dear Shaowu
Thanks so much
Best wishes
Rob

 

[shaowu459]

Dear Shaowu
Thanks so much
Best wishes
Rob

you are welcome and thanks for the feedback