• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

How do I copy this formula to the rest of the sheets but have it automatically reflect the month before?

Hi all,

I am working in Excel 07.  I have 12 sheets - January, February, March...etc.  I have a number of formula in the second sheet that read =SUM(AH22+January!AH23)


This formula adds up a year-to-date balance.  I would like to copy this formula to the rest of the sheets but have it automatically reflect the month before.  For example, the March sheet should read

=SUM(AH22+February!AH23).

When I do a copy - past, paste special, or ctrl and pointer method it doesn't change the month to reflect the next worksheet; I hope this makes sense.  Anyway, I know that it is no big deal to manually type these formulas in.  I am just wondering if there is a way to do it automatically.

Any suggestions would be greatly appreciated.

LGX
0
Shaft960
Asked:
Shaft960
  • 2
  • 2
2 Solutions
 
Shaft960Author Commented:
To clarify, I have a couple of formulas that refer to the previous month: not just   =SUM(AH22+January!AH23)
0
 
SiddharthRoutCommented:
The easiest way is to make copies of the second sheet and use a small macro to find and replace all the names. Would that help?

Sid
0
 
SiddharthRoutCommented:
Here is a sample file attached. Every sheet has "January" in it's formula. That can be achieved like I mentioned above. Simply copy from 1st sheet and paste in the rest.

Please run the macro Sub ChangeMonths()

Sid

Code Used

Sub ChangeMonths()
    Dim SearchString As String
    Dim ReplaceWith As String
    
    SearchString = "January"
    
    For i = 1 To ActiveWorkbook.Sheets.Count
        If Sheets(i).Name <> "January" Then
            ReplaceWith = monthName(convertMonthName2Number(Sheets(i).Name) - 1)
            
            Sheets(i).Cells.Replace What:=SearchString, Replacement:=ReplaceWith, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        End If
    Next
End Sub

Function convertMonthName2Number(monthName As String) As Integer
    Dim dtestr As String, dte As Date
    
    dtestr = monthName & "/1/2011"
    
    On Error Resume Next
    dte = CDate(dtestr)
    
    If Err.Number <> 0 Then
        convertMonthName2Number = -999
        Exit Function
    End If
    On Error GoTo 0
    
    convertMonthName2Number = Month(dte)
End Function

Open in new window

ChangeMonthInFormula.xls
0
 
Rob HensonIT & Database AssistantCommented:
You could set up a summary sheet that pulls the individual month values from  the sheets and then the formula on each sheet could be a sumif or something similar with the criteria being month number based.

Cheers
Rob H
0
 
Shaft960Author Commented:
Thank you, much obliged.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now