Totalling sequential worksheets

[KErin]

I want to set up a generic formula that automatically looks for a particular
cell in the previous worksheet & adds 1 to the value. When I copy & paste the
formula, it copies exactly, instead up updating to the previous worksheet,
even when I try copying only the formatting or formula.

Example:
In worksheet January 13, 2008, the formula is: =SUM('Dec 9, 2008'!M2+1) &
the value is 7
In the worksheet February 10, the formula is: =SUM('Jan 13, 2008'!M2+1) and
the value is 8
When I create the new worksheet March 10, 2009, I copy the cell from the
February worksheet & paste it into the March work sheet, it comes out as:
=SUM('Jan 13, 2008'!M2+1) with a value of 8
I want it to come out as: =SUM('Feb 10, 2009'!M2+1) with a value of 9.

And so on.... so each total value in cell M2 gets greater in increments of
1, & that it calculates it without me having to create the formula for that
cell each time - because there are approximately 25 cells in each worksheet
that need to be updated in this way.
Anybody got a clue? Thanks!

 

[Bernard Liengme]

This subroutine is copied from
http://www.exceltip.com/st/Add_valu...rksheet_using_VBA_in_Microsoft_Excel/516.html


Function SumPreviousSheet(InputRange As Range) As Double
' adds the values in InputRange in the previous worksheet
' returns 0 if the function is used in the first worksheet
Application.Volatile ' omit if not necessary
SumPreviousSheet = 0
On Error Resume Next
SumPreviousSheet = _
Application.WorksheetFunction.Sum(InputRange.Parent.Previous.Range(InputRange.Address))
On Error GoTo 0
End Function

Use is like this =SumPreviousSheet(M2)+1

New to VBA?
VBA intro
Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm


By the way: you do mot need SUM when you are just adding two values
So =SUM('Dec 9, 2008'!M2 + 1) can be written as ='Dec 9, 2008'!M2 + 1