self-adjusting summation formula when cell rows are added

[aletheapyre]

I very rarely use excel, so if theres an easy solution to this, feel free to pat me on the head.

I have a very basic excel inventory tally sheet, which has a number of subcategories (ie properties, investments, debts, etc). The item value in each subcatagory needs to be summed up.

Now, I can manage this, but the problem is this: the basic template only has 1 row in each subcategory, and when you add more rows, they wont add correctly in the total cell. So I want a formula that will add the 1 row with any subsequent rows that are inserted.

I want to keep things as simple as possible, as the people who will use this sheet are even worse at excel than I am.

Edited July 25, 2007 by aletheapyre

[jaclaz]

There are two easy workarounds:

1.

add a blank row just above the SUM() function.

Say the the data is in B1 and the SUM function is in B2 as SUM(B1:B1).

When you add a row between 1 and 2 the SUM formula will go in B3 but arguments will remain (B1:B1).

Now change formula in B3 from SUM(B1:B1) to SUM(B1:B2) or, better yet, to SUM($B$1:$B$2).

From now on, every time you add a line immediately below last data entered, NOT immediately above total, formula will change accordingly.

2.

Change the SUM function into the cell B2 from SUM(B1:B1) to SUBTOTAL(9;B1:B2) or =SUBTOTAL(9;$B$1:$B$2) i.e. with the range comprising the cell where the subtotal function is.

From now on, every time you add a row, whether immediately after the first row or anywhere between first row and the total row, formula will change accordingly.

jaclaz

Edited July 26, 2007 by jaclaz

[aletheapyre]

Thanks for your prompt response!

I had some problems when I tried your suggestions. I hope your or someone else can suggest solutions.

1. I thought of this, but unfortunately the form has some very strict requirements, and I cannot have a blank line between the subtotal and the the items when we submit it. As the forms are frequently amended, if someone initially deletes the extra row, then adds it back later, the function will not work. If I change the size of the extra row to something very small, then my people would have difficulty adding rows above it. I dont think this option will will be practical.

2. I really like this suggestion, but when I tried it said I was creating a circular reference (which is true). How can I fix this?

[jaclaz]

2. I really like this suggestion, but when I tried it said I was creating a circular reference (which is true). How can I fix this?

What you report is very strange.

The subtotal function does not create a circular reference.

Maybe is something else creating it.

Try the attached file and tell me if it works for you or not.

jaclaz

testp.zip