Summing across multiple sheets

[bqheng]

Hi,

I have this workbook that tracks leave and time off. In Column D of the Time off Balance tab, you will see a formula that I used to calculate the balance of time off each staff has. This requires the specific row the name of the staff is in to be entered in the formula. Problem arises when I slot in new staff in between current staff and the row number changes. Im think of using SUMPRODUCT, but do not know how to input the correct formula. Can anyone help me with this please?

Attached is the file

[mehmetcik]

Why not use a Macro to insert a new row into every sheet and copy your new name and your formulas to all sheets?

[bqheng]

Hi mehmetcik, thank you for helping. How do you propose to do that? I have reuploaded the same file without protection

[mehmetcik]

This would normally do the trick

but for some reason it doesn't work on your workbook.

I can select a single row manually

but not with a macro. I have no explanation. Perhaps you do.

'These do not work

Rows(R).Select
Rows(R & ":" & R).Select
Rows("36:36").Select

Sub Macro1()
Dim flag As Boolean
Dim Count, R As Integer

Application.ScreenUpdating = False
Application.EnableEvents = False


flag = False
For Count = 1 To 12
If ActiveSheet.Name = MonthName(Count) Then flag = True: Exit For
Next

R = ActiveCell.Row

If flag = False Or R < 11 Then Exit Sub

For Count = 2 To 12
Sheets(MonthName(Count)).Select
Rows(R).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Next

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

[bqheng]

Hi, the codes were written by someone else, so I do not have any explanation either. How about we not use macro and use formulas instead? will that be easier?