sum

  • Thread starter Thread starter Andrea
  • Start date Start date
A

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
 
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
 
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)
 
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
Click to expand...
cell. Say your current area is A1 through A10 and your
SUM is in A11. then use:
 
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)
 
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
Click to expand...
cell. Say your current area is A1 through A10 and your
SUM is in A11. then use:
 
Back
Top