Can I disable auto formula update?

[Guest]

We have a number of multi-year spreadsheets for collecting and analyzing
monthly costs for a small business. Every month, we delete the oldest month
and insert a new month (delete a row or column and add a new one at the
opposite end of the spreadsheet). Excel, however, automatically changes our
formulas (total cost, average cost, total one year ago, etc.) because the
data moved on the spreadsheet; and we have to manually update all the
formulas. Is there any way to stop auto formula update?

 

[Guest]

Include an extra blank row/column in your formula, this way, when deleting
the last row/column on data, the actual last row/column will not be affected.

 

[Guest]

That's one of the first things we tried. Problem is, when we delete the
first row or column and add a new row or column at the end of the
spreadsheet, the formulas automatically update because the data has moved.
What we are looking for is something that will stop the auto formula update
so we don't have to go back and correct the formulas every month.
Thank you for your idea.
JCH

 

[Alex Delamain]

Here's a suggestion - try using =offset() in your formulae.
For example:
If your data has column headings in row 1 add a new row above it. The
in cell A1 enter =counta($a:$a)-2 (the -2 is to accoun
for the cell itself and the heading)

This will give the number of rows of data and will update when rows ar
added or deleted.

For your formulae you then use this number of rows in an offse
function
=offset(reference,rows,cols,height,width)
so if column B has monthly sales starting in B3 the last 12 month
total is:

=SUM(OFFSET(B2,$A$1,,-12))

No matter how many rows are added or removed this will always sum th
last 12 rows

hope it help

 

[Guest]

Thanks Alex. I'll give it a try.
jch