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?

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.
--
http://HelpExcel.com
1-888-INGENIO
1-888-464-3646
x0197758


"jch" wrote:

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?

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

"galimi" wrote:

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.
--
http://HelpExcel.com
1-888-INGENIO
1-888-464-3646
x0197758


"jch" wrote:

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?

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. Then
in cell A1 enter =counta($a:$a)-2 (the -2 is to account
for the cell itself and the heading)

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

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

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

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

hope it helps


--
Alex Delamain
------------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273
View this thread: http://www.excelforum.com/showthread...hreadid=394062

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

"Alex Delamain" wrote:


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. Then
in cell A1 enter =counta($a:$a)-2 (the -2 is to account
for the cell itself and the heading)

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

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

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

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

hope it helps


--
Alex Delamain
------------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273
View this thread: http://www.excelforum.com/showthread...hreadid=394062