Automatic updattion of a formula

Hi All,


I have data in P21 to P44
I sum it using a formula "=SUBTOTAL(9, P21:P44)" in row 45.

I inserted 10 rows using a macro and pasted some data in these newly inserted rows by opening another workbook using the macro.

My formula is not changing. It remains same.
I want this formula as "=SUBTOTAL(9, P21:P54)

How can I achieve this?

Thanks in advance.

Hey sweep,

Thanks for the quick response.

No. I haven't turned it off both in options as well as in macro.

From another forum I gt following help:

Define a name as "TotRange" with Refers to value as below

=OFFSET('Capex Details'!$P$21,0,0,COUNTA('Capex Details'!$P24:$P3),1)
Then enter formula as "=SUBTOTAL(9,TotRange)"

This works properly, but results in circular reference if I put this formula just below the last row i.e. in P45

Just sharing this so that this might give some clue.

Thanks once again.

nik

Something's not right for sure! Can you post your worksheet?

Hey Sweep,

This problem is solved.

Defined a name "Arange"with range as below in refers to:

=Sheet1!$P$21:P44

Note:- in the range last cell address is without any $ sign.

Then in row 45 where the total is required.. entered the following formula..

=SUBTOTAL(9, Arange)

Similarly, separate names defined for each column such as Brange, Crange.... Zrange etc.

That's all. This solved my problem. After adding some more rows & pasting the data in those newly inserted rows, the code in "Arange" changed automatically as =Sheet1!$P$21:P54

I had posted this problem on Mr Excel forum. There I got some clues. I developed it further. Thanks to all those supported me in Mr. Excel forum and this forum too.

Thanks for your efforts.

nik