Private budget change start month with drop down box Excel 2007

I want to make my home budget a little smarter.
I want to add a function that makes it possible to change the start month in my budget sheet (Budget sheet with possibility to change start month) from a drop down box. When start month changed I want the starting month column cells to point at cells from the chosen starting month on another budget sheet (Main budget sheet) that holds the income and expenses.
I have seen this on my internetbank and I would like to be able to fast see how my budget looks when another starting month is chosen.
I also would like to choose the other 11 months from drop down boxes and get the data from the main budget corresponding to the month chosen. It would be smart if it could automatically select the correct months in the next 11 columns when starting month is chosen. I guess it could be som simple VB programming?

I appreciate all the help you can give me as this something I really would like to add.


Kind Regards Christian

Hi Christain,

I have attached two files. both have same end result. see if the is what you are looking for.

BudgetHlookup.xls
I added validation list in cell B1 for selecting the starting month and then whole bunch of Hlookup formula in all the cells for data. Then added code in 'Sheet with changing month' that will autofill the months in the first row.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$B$1" Then
    Application.EnableEvents = False
    Target.AutoFill Destination:=Range("B1:M1"), Type:=xlFillDefault
    Application.EnableEvents = True
End If
End Sub

Budget-CopyPaste.xls
at first i wrote the code in this file that copied data from Sheet1 to Sheet2 based on the month, but then i realized that if you change data in the Main Budget sheet, it will not be updated on Sheet2 until you run the code again. So I thought it's better to have lookup formula, then the data in Sheet2 is always current. If you want you can still use this file by adding more event handlers. the code in the file is

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Check
Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Address = "$B$1" Then

    Target.AutoFill Destination:=Range("B1:M1"), Type:=xlFillDefault
    For i = 2 To 13
        Set c = Sheet1.Range("1:1").Find(Cells(1, i), LookIn:=xlValues)
        If Not c Is Nothing Then
            fcell = c.Offset(1, 0).Address
            lcell = Sheet1.Range(Cells(6000, c.Column).Address).End(xlUp).Address
            Sheet1.Range(fcell & ":" & lcell).Copy
            Sheet2.Range(Cells(2, i).Address).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
        Else
            MsgBox "The month '" & Cells(1, i) & "' was not found in the Look up data.", vbCritical, "Month not found!"
        End If
    Next

End If

Resume_sub:
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

Err_Check:
MsgBox Err.Description, vbCritical, "Error Found"
GoTo Resume_sub

End Sub

Hope that helps.

Hey Keyur

Thanks a lot!!!! I will try it and then I get back to you. It is just great you took the time to help me to making a good budget sheet :-).

Kind Regards
Christian

Hi Keyur,

I have exactly the same doubt as Christian. But I'm not able to open your files. Do you still have them? Your answer is dated from May 2009...

Many thanks,
Ana.

Hi Keiur,

I have exactly the same doubt as Christian. But I'm not able to open your files. Do you still have them? Your answer is dated from May 2009...

Many thanks,
Ana.