Inserting row
 

Hi,

I can't figure out why is it that in some workbooks that I have, when I
enter additional row above Total row (which calculates sum), that Total row
includes the row I inserted, but some don't.
Example: I have invoices coming that I have to insert every day, so when I
insert another row for an invoice, cell that has SUM(D3:D25), doesn't change
to SUM(D3:D26). Sometimes it does, but only when I enter an amount into the
cell of inserted row, but in this particular workbook, it doesn't.
I need help with this, beacuse I don't want to have to change range every
time I insert a row.

Thanks a lot.

Inserting row
 

The problem only occurs when you insert new rows at the edge of your sum
range. If this bothers you, you could try a different approach to
summing.

Instead of placing =SUM(D3:D25) in D26, replace it with this formula:
=SUM(D3:offset(D26,-1,0))

Then you don't need to change it when new rows are inserted there.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532549

Inserting row
 

It works for me (insert, and on enter amount it adjusts the formula)
*unless* I insert multiple lines and complete them out of sequence.
Then, once the sequence is broken, the formula remains at fault until
the formula is amended.

Would that be your reason too?

--

umba-sr Wrote:
Hi,

I can't figure out why is it that in some workbooks that I have, when
I
enter additional row above Total row (which calculates sum), that Total
row
includes the row I inserted, but some don't.
Example: I have invoices coming that I have to insert every day, so
when I
insert another row for an invoice, cell that has SUM(D3:D25), doesn't
change
to SUM(D3:D26). Sometimes it does, but only when I enter an amount into
the
cell of inserted row, but in this particular workbook, it doesn't.
I need help with this, beacuse I don't want to have to change range
every
time I insert a row.

Thanks a lot.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=532549

Inserting row
 

If you put this formula directly under the list in column D you want to sum,
it will always sum from D3 to the cell directly above it. I do a lot of cut
and pasting and constantly have to change my formulas unless I plan ahead and
insert my formulas like this.

=SUM(INDIRECT("R3C4:R[-1]C4",0))

"umba-sr" wrote:

Hi,

I can't figure out why is it that in some workbooks that I have, when I
enter additional row above Total row (which calculates sum), that Total row
includes the row I inserted, but some don't.
Example: I have invoices coming that I have to insert every day, so when I
insert another row for an invoice, cell that has SUM(D3:D25), doesn't change
to SUM(D3:D26). Sometimes it does, but only when I enter an amount into the
cell of inserted row, but in this particular workbook, it doesn't.
I need help with this, beacuse I don't want to have to change range every
time I insert a row.

Thanks a lot.