PDA

View Full Version : Solved: Update Formulas to include added rows



RECrerar
03-26-2008, 06:49 AM
Hi, I'm wondering how to get a formula to update automatically when a new row of data is inserted.

Basically I have a worksheet that the user enters some data into manually and then a couple of simple formulas are applied to the equations. However if someone inserts a new row or data at the end or a summed range (rather than in the middle of it), the formula does not automatically update to include this, which is slightly annoying.

I've attached a workbook which should make things easy to view. (Please ignore all the strange titles and the fact that you are never likely to need to find the root-sum-square of the amount of dog people own, I just knocked up a dummy workbook in the same format as the original, but with different titles and values, The formulas are all the same as the original).

To test the problem, try inserting a row at row 7 and then entering data, you will see that an alert come up giving you the option to update the formula but teh formula does not update on it's own.

It's probably really simple and I'm sure I could do it with a macro but I would rather not use a macro as possible to keep the spreadsheet calculations easily understandable to the other users.

Sorry this is a bit waffly, hope it makes sense. Regards, Robyn

Bob Phillips
03-26-2008, 06:58 AM
An example

C8: =SQRT(SUMSQ(C4:OFFSET(C8,-1,0)))

RECrerar
03-26-2008, 07:06 AM
Knew it would be easy, thanks