Help on proper use of subtotals

[Gringoire]

Hello guys,

I'm preparing a template that will be distributed to several Users.
In one of the sheets there is a long sum splitted in "groups". Each group has a description row and a subtotal.

Let's say that this is a group:
- K7 is the description
- K8 ... K15 are free for introducing data.
- K16 is the subtotal. The formula in cell K16 is simply

=SUBTOTAL(9,K7:K15)

The User is (must be) allowed to insert extra rows in the group if necessary, and this works perfectly until the User select the subtotal row (K16) to insert a new row immediately before it.
In this case (and only in this case) the data belonging to this new row are NOT included in the subtotal, and this causes errors.

How could I modify the above mentioned formula in order to avoid this unwanted behavior?
For several reasons I prefer to avoid the use of VBA.

thank you.

 

[Sanjeev1976]

Hi Gringoire,

What I do in such situations is to add an extra row just before the sub-total formula and keep it with a height of 5 and this automatically cautions the user to add any row only above that and the formula will dynamically keep on changing. So instead of subtotal formula in K16, I will put the formula

=SUBTOTAL(9,K7:K16)

in cell K17

 

[Gringoire]

Yes, it is a nice idea!
I can also protect the extra row from writing, preventing the User to input data in that row.

thanks.