Relative Cell Reference
 

The fill handle allows me to create a function in a cell (a1) and drag it to
cell (a2). With relative referencing the cells referenced in my function
will also move a single row down.

Is there a way to use relative referencing to move a single row down, but
change the function's reference by 5 units?

ie function in cell a1 is sum(b1:b5)
I want cell a2 to say sum(b6:b10)

all I seem to be able do is to get a2 to say sum(b2:b6)

Relative Cell Reference
 

ie function in cell a1 is sum(b1:b5)
I want cell a2 to say sum(b6:b10)

One way to achieve it ..

Put in A1:
=SUM(OFFSET(INDIRECT("B"&ROW(A1)*5-5+1),,,5))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Keith" wrote:
The fill handle allows me to create a function in a cell (a1) and drag it to
cell (a2). With relative referencing the cells referenced in my function
will also move a single row down.

Is there a way to use relative referencing to move a single row down, but
change the function's reference by 5 units?

ie function in cell a1 is sum(b1:b5)
I want cell a2 to say sum(b6:b10)

all I seem to be able do is to get a2 to say sum(b2:b6)

Relative Cell Reference
 

And of course, the non-volatile alternative:

=SUM(INDEX(B:B,5*ROWS($1:1)-4):INDEX(B:B,5*ROWS($1:1)))

And copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Max" wrote in message
...
ie function in cell a1 is sum(b1:b5)
I want cell a2 to say sum(b6:b10)

One way to achieve it ..

Put in A1:
=SUM(OFFSET(INDIRECT("B"&ROW(A1)*5-5+1),,,5))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Keith" wrote:
The fill handle allows me to create a function in a cell (a1) and drag
it to
cell (a2). With relative referencing the cells referenced in my
function
will also move a single row down.

Is there a way to use relative referencing to move a single row down,
but
change the function's reference by 5 units?

ie function in cell a1 is sum(b1:b5)
I want cell a2 to say sum(b6:b10)

all I seem to be able do is to get a2 to say sum(b2:b6)