insert cell problem

[stone]

Hi guys.

I cells A1:A10 i have numbers. And in cell B1 I have the formula =SUM($A$1:$A$10),
My problem is, when I select cell A1 and do
Insert->Cell shift cells down), Then my formula in cell B1 changes to
=SUM($A$2:$A$11), BUT I dont want that to happen, it should stay the same(=SUM($A$1:$A$10))?

ANY ideas?
(ps. this is a simple version of my problem)

BK
Stulli

[elgringo56]

I had the same problem and solved it like this. Hope this helps

Range("A1:B1").Select
Selection.Insert Shift:=xlDown
Range("B2").Select
Selection.AutoFill Destination:=Range("B1:B2"), Type:=xlFillCopy
Range("B1:B2").Select
Range("B2").Select
Selection.ClearContents
Range("A1").Select

[Chris Davison]

=SUM(INDIRECT("A1:A10"))

for a formula solution





_________________
Hope this helps,
Chris
(Excel '97, Windows ME)

[ This Message was edited by: Chris Davison on 2002-05-26 05:06 ]

[Robb__]

You could also try OFFSET:

=SUM(OFFSET(B1,0,-1,10,1))

This will also always refer to A1 to A10 ie as you add rows, the lower ones cease to be included in the calculation.

Regards

Robb__