Hi Max

Thanks for your response. To be honest, I had never noticed that there
was a ROW() and a ROWS() function, and you are absolutely right, with
ROWS, then the $A$1:A1 notation does do the summation.

I still question the validity of this providing the correct solution
however, when rows are inserted.

For example, with 10,20,30,40,50,60,70,80,90,100 in cells A17:A26 and
the formula in
B16 =SUM(INDIRECT("$A$17:A"&ROW()))
C16 =SUM(INDIRECT("$A$17:A"&ROWS(A1:A1)+16))

Copying these down through rows 18:26 gives the following results
10,30,60,100,150,210,280,360,450,550
10,30,60,100,150,210,280,360,450,550

Now, insert a new row at row 14, (data now in A18:A27 and formulae moved
accordingly) and I get the following results
10,30,60,100,150,210,280,360,450,550
0,10,30,60,100,150,210,280,360,450

Now insert a new row at 21 and I get
10,30,60, ,100,150,210,280,360,450,550
0,10,30, ,60,60,100,150,210,280,360

So I still question that this form of offset provides a more robust
solution, or am I totally missing something?

Regards

Roger Govier



Max wrote:

Thanks for the response, Roger. My apologies for the confusion. The
mistake was all mine. I just realized that I had made a *major* goof in
that last response, it should have read as:

ROWS($A$1:A1)+3
not
ROW($A$1:A1)+3
(there was a missing "S" everywhere, sheesh ..)

So the suggested formula should have read as:

=IF(G4-J4<0,G4-J4,G4-J4+
INDIRECT("'"&PrevSheet()&"'!H"&ROWS($A$1:A1)+3) )

and the line


The slightly longish incrementer: .. ROW($A$1:A1)+3



should have read as:


The slightly longish incrementer: .. ROWS($A$1:A1)+3



--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--