Hi Harlan

Thanks for that, it is much better and it is totally robust.

=SUM(OFFSET(SheetX!$A$17,0,0,ROWS(SheetX!A$17:A17) ,1))

One minor amendment, to pick up the 10 row range it needs modifying to

=SUM(OFFSET(SheetX!$A$17,0,0,ROWS(SheetX!A$17:A26) ,1))


Regards

Roger Govier



Harlan Grove wrote:

Roger Govier wrote...


I can see that, and at first sight that sounds great.
But, and I'm sorry to be pedantic, try inserting a new row at Row2.


....


Therefore, the use of the Absolute $A$1 only protects against the
insertion at Row 1, but does nothing for protecting any other
insertion(s) compared with using just Row() and as such I really cannot
see that it is worth elongated the formula just for this single case.


....

Start off with your original setup, {10;20;30;40;50;60;70;80;90;100} in
A17:A26. Then write the textrefs starting in B17:E17 (I'm adding one).

B17:
="A17:A"&ROW()

C17:
="A17:A"&ROW(A1)+16

D17:
="A17:A"&ROWS(A$1:A1)+16

E17:
="A17:A"&ROWS(A$1:A17)

Insert a row at row 14. All the text refs still begin in row 17. That's
an error in all of them if there were any number in A16 initially.

The *safe* way to do this is to avoid using INDIRECT in the first
place. This is MUCH BETTER done using OFFSET.

=SUM(OFFSET(SheetX!$A$17,0,0,ROWS(SheetX!A$17:A17 ),1))

Now insert or delete rows anywhere.