Formula doesn't adjust when Inserting a row into a range

  • Thread starter Thread starter David
  • Start date Start date
D

David

I am building a budget using outlining in order to show
detail for a particular total. I want to start out the
budget with only one detail line per budget, and then
write a macro to allow the user to insert a detail line.

The problem I have is that when I use the insert command
the formula for the total below the detail line doesn't
adjust to include the new detail line.

Any ideas? I would really appreciate the help.

David
 
Hi David,
You should start with 2 detail lines for each subtotal.
When you insert a row do it between the 2. Never insert a
row as the first or the last it will never be included in
your range
ray
 
This is cheating, but what I do is to leave an extra blank
row and insert the new row between the last row that has
data and the blank row, so that when you insert, the new
row is within the range in the original formula. Got it?
 
Thanks. That's a clever work-around.

David
-----Original Message-----
This is cheating, but what I do is to leave an extra blank
row and insert the new row between the last row that has
data and the blank row, so that when you insert, the new
row is within the range in the original formula. Got it?

.
Click to expand...
 
You could adjust your formula:

instead of this in A12: =SUM(A1:A11)
you could use: =SUM(A1:OFFSET(A12,-1,0))
 
I'm looking to do essentially the same thing as the OP. This produced
the same result as the original.

I have several rows of data. I want to total several of the columns.
I'm using =SUM(F7:F33) now. =SUM(F7:OFFSET(F33,-1,0)) returns the
same total as =SUM(F7:F33). If I add row 34 the sum remains the same.
I'm sure I'm not using OFFSET correctly here. What I'm not sure of is
whether it will work to achieve the desired results. I'd like Excel
to sup up all the rows in a given column even if I take rows on the
end. I understand I can add rows before the last one in the range and
Excel will add them in. I'm just trying to find if there's a way to
add them at the end and still have it Excel add the whole column
 
Maybe it's as easy as where you put the formula:

I put this in F33:
=SUM(F7:OFFSET(F33,-1,0))
And inserted a new 33 and things got adjusted correctly.

I'm not sure if this is a better fit for you, but it's simpler. I put my totals
in the top row. I freeze the panes so that those totals are always visible,
too.

In fact, I like to apply Data|Filter|autofilter, so I use =subtotal() so that it
reacts to the filtering, too.
 
Back
Top