Hi Max

I may be wrong, but I don't think you need the $ around the first part
of the reference., I think it should be
ROW(A1:A1)+3 if it is to increment properly as you copy down.

Also, I am interested in your assertion that it makes the formula more
robust against row insertions.
In my experience, I get errors when using your method if rows are then
inserted, whereas, I don't get an error when I just use ROW().

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"&ROW(A1:A1)+16))
D16 =SUM(INDIRECT("$A$17:A"&ROW($A$1: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
10,10,10,10,10,10,10,10,10,10

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
0,0,0,0,0,0,0,0,0,0

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
0,0,0, ,0,0,0,0,0,0,0

What am I doing differently to you?

Regards

Roger Govier



Max wrote:

Glad you got the incrementer part working
from Roger's suggestion (Thanks, Roger !)

Perhaps just a slight refinement to that would be to use
in the starting cell (which may not necessarily be in row 4):

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

Then the formula can be copied down to increment nicely as required

The slightly longish incrementer: .. ROW($A$1:A1)+3
will evaluate to: 1 + 3 = 4 in the starting cell
(the "3" is just an arithmetic adjustment to make the start number 4)
and, when copied down, would evaluate to: 5, 6, 7 ...

The expression is also robust against any subsequent row insertions
which may happen above the starting cell compared to using ROW()
(just one way I picked up from here and adopted since)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--