how to make a cell forumla stick when inserting a row above it

[mackconsult]

I have some forumlas at the bottom of a spreadsheet. The make summations of the columns of the spreadsheet. When I insert a new row at the top to create another entry it changes the forumla like so:

summed value=SUM($B$2:B455)

If I insert a new row at the top it changes to:

summed value==SUM($B$3:B456)

I want the 2nd reference to change (B455 to B456). But I want the first reference to always be B2, is there a good way to setup the formula for this?

Thanks for any input.

[JLGWhiz]

If you make B2 a named range then you can insert rows and it will only change the second part of the range reference. eg if B2 is named 'test' Then

Please Login or Register  to view this content.

Should only change the B455 to B456 when a row is inserted above row 455.

[TMS]

Maybe

Please Login or Register  to view this content.

[TMS]

If you make B2 a named range then you can insert rows and it will only change the second part of the range reference.

No. The Named Range will move down as rows are inserted above it.

[JLGWhiz]

No. The Named Range will move down as rows are inserted above it.

True if you insert above B2 and I suppose that was what the OP was doing. I just overlooked that fact.

[TMS]

Yes. The OP did say

If I insert a new row at the top it changes to:

summed value==SUM($B$3:B456)

[AliGW]

The easiest way to deal with this is to convert the range to a table and then use the SUBTOTAL function at the foot of the table. e.g:

=SUBTOTAL(9,[Column1])