insert row and copy formula
 

Hello,
I have been reviewing previous questions and answers and wasn't able to
locate the answer to mine. If someone could please assist me that would be
greatly appreciated. I have provided the problem below. When I insert a row
under a3 the formula is not automatically incorporated. I have tried the
data--list option and tools-- options. How can i solve this problem so
when a row is inserted at the bottom of the formula range the formula in say
"A1" will autmotically extend

the problem:
A
1 SUM(a2:a3)
2 1
3 2
4sum(A5:A7)
5 4
6 6
7 8

thanks,
Andy

insert row and copy formula
 

one way:

A1: =SUM(OFFSET(A1,1,0,ROW(A6)-1,1))

In article ,
Andy B wrote:

Hello,
I have been reviewing previous questions and answers and wasn't able to
locate the answer to mine. If someone could please assist me that would be
greatly appreciated. I have provided the problem below. When I insert a row
under a3 the formula is not automatically incorporated. I have tried the
data--list option and tools-- options. How can i solve this problem so
when a row is inserted at the bottom of the formula range the formula in say
"A1" will autmotically extend

the problem:
A
1 SUM(a2:a3)
2 1
3 2
4sum(A5:A7)
5 4
6 6
7 8

thanks,
Andy

insert row and copy formula
 

hI JE,

I have tried the formula but it doesn't seem to work. I'm not sure but the
A6-1 caputures the values in cell A5. I just want the formula in A1 to
capture the amounts in A2:A3, and if someone were to add a row below the A3
row it would the new A4 the formula in A1 will read sum(A2:A4). Thereafter
the formula in the A4 cell prior to the insert would would move down.

so IF insertrow below A3 then the formula in A1 includes new A4

and

If insertrow below A7 then the formula in new A5 drags to A8

Your help is greatly appreciated

"JE McGimpsey" wrote:

one way:

A1: =SUM(OFFSET(A1,1,0,ROW(A6)-1,1))

In article ,
Andy B wrote:

Hello,
I have been reviewing previous questions and answers and wasn't able to
locate the answer to mine. If someone could please assist me that would be
greatly appreciated. I have provided the problem below. When I insert a row
under a3 the formula is not automatically incorporated. I have tried the
data--list option and tools-- options. How can i solve this problem so
when a row is inserted at the bottom of the formula range the formula in say
"A1" will autmotically extend

the problem:
A
1 SUM(a2:a3)
2 1
3 2
4sum(A5:A7)
5 4
6 6
7 8

thanks,
Andy

insert row and copy formula
 

Try:

A1: =SUM(OFFSET(A1,1,0,ROW(A4)-ROW(A1)-1,1))
A4: =SUM(OFFSET(A4,1,0,ROW(A8)-ROW(A4)-1,1))

In article ,
Andy B wrote:

hI JE,

I have tried the formula but it doesn't seem to work. I'm not sure but the
A6-1 caputures the values in cell A5. I just want the formula in A1 to
capture the amounts in A2:A3, and if someone were to add a row below the A3
row it would the new A4 the formula in A1 will read sum(A2:A4). Thereafter
the formula in the A4 cell prior to the insert would would move down.

insert row and copy formula
 

Hi JE,

Thanks for this it worked... appreciate the help

Cheers

"JE McGimpsey" wrote:

Try:

A1: =SUM(OFFSET(A1,1,0,ROW(A4)-ROW(A1)-1,1))
A4: =SUM(OFFSET(A4,1,0,ROW(A8)-ROW(A4)-1,1))

In article ,
Andy B wrote:

hI JE,

I have tried the formula but it doesn't seem to work. I'm not sure but the
A6-1 caputures the values in cell A5. I just want the formula in A1 to
capture the amounts in A2:A3, and if someone were to add a row below the A3
row it would the new A4 the formula in A1 will read sum(A2:A4). Thereafter
the formula in the A4 cell prior to the insert would would move down.