Relative formula's by VBA

Hi Guys,

I've got a work related problem I can't seem to solve. I've set up a spreadsheet as a user input form, that copies data to a database. Method:

Set DatabaseSheet = Workbooks.Open("{path goes here}", , , , "{password goes there}")

With DatabaseSheet
    With .Sheets(5).Cells(Rows.Count, 1).End(xlUp)(2)
.Value = UserInput.Sheets(1).Range("J2")
.Offset(, 1) = UserInput.Sheets(1).Range("B4")
.Offset(, 2) = UserInput.Sheets(1).Range("J1")

' etc.

What i need is two coloums in the database in order to report on the input data.

- In column BT I need the formula "=SUMPRODUCT($BV$2:$EH$2;E2:BQ2)", where BV2:EH2 is an absolute reference, but the tricky part (for me) is that I want E2:BQ2 to be relative to the row the data gets placed in.
- In column BU I need the forumula "=VLOOKUP(MONTH(D2);$BW$5:$BX$16;2;FALSE)&" "&YEAR(D2)", again with relative references to the row that the data gets placed in.

I'm stuck trying to get the relative references to work. I've tried

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Application.EnableEvents = False
        Target.Offset(0, 72).ActiveCell.FormulaR1C1 = "=SUM(OFFSET(R1C5,COUNTA(C[-72])-1,,,65))"
        Application.EnableEvents = True
    End If
End Sub

Plus a few variations on this, but I seem to be going nowhere. Can you help me out?