sum

[Andrea]

i am having a problem with a spreadsheet i designed. i
made a macro which inserts a row at the bottom of the
sheet and autofills the corresponding cells with the
corresponding formulas. my problem is that i have a
sumation at the end of one of the columns (right under the
inserted row), and it refused to take into account the new
row. how can i fix this?
thank you for your time.
andrea

 

[Mark Graesser]

Andrea,
Change your SUM formula to use an OFFSET for the bottom cell. Say your current area is A1 through A10 and your SUM is in A11. then use:

=SUM(A1:OFFSET(A11,-1,0))

Good Luck,
Mark Graesser
(e-mail address removed)

----- Andrea wrote: -----

i am having a problem with a spreadsheet i designed. i
made a macro which inserts a row at the bottom of the
sheet and autofills the corresponding cells with the
corresponding formulas. my problem is that i have a
sumation at the end of one of the columns (right under the
inserted row), and it refused to take into account the new
row. how can i fix this?
thank you for your time.
andrea

 

[Bob Phillips]

Andrea,

Change the SUM formula to use OFFSET. For instance, if your formula is in
A30 and is =SUM(A1:A29), change it to

=SUM(A1:OFFSET(A30,-1,0))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Andrea]

Mark,
using the offset formula is not working with my macro. is
there any other way you know how to do it? because it
works when i insert the first line, but as i insert more,
the A11, for instance, remains the same, so it always
offsets to the same cell.

-----Original Message-----
Andrea,
Change your SUM formula to use an OFFSET for the bottom

cell. Say your current area is A1 through A10 and your
SUM is in A11. then use:

 

[Bob Phillips]

Andrea,

Let's see the macro code and more details of what is happen ing.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mark Graesser]

Andrea,
Is the formula already in the cell, or is it entered by your macro? Post the code so we can see what you are trying to do.

Regards,
Mark Graesser
(e-mail address removed)

----- Andrea wrote: -----

Mark,
using the offset formula is not working with my macro. is
there any other way you know how to do it? because it
works when i insert the first line, but as i insert more,
the A11, for instance, remains the same, so it always
offsets to the same cell.

-----Original Message-----
Andrea,
Change your SUM formula to use an OFFSET for the bottom

cell. Say your current area is A1 through A10 and your
SUM is in A11. then use: