Change your definition of names into a dynamic range.
use this as your definition:-
Formula:
=Names!$A$2:INDEX(Names!$A$1:$A$2000,MATCH("zzz",Names!$A$1:$A$2000),1)
Similarly use this as your definition for tasks:-
=Tasks!$A$1:INDEX(Tasks!$A$1:$A$2000,MATCH("zzz",Tasks!$A$1:$A$2000),1)
And for Teams:-
=Names!$E$2:INDEX(Names!$E$1:$E$2000,MATCH("zzz",Names!$E$1:$E$2000),1)
To automate column B.
Right click on the sheet at the bottom of excel and paste this code in the window that opens.
close the window.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Or Target.Row = 1 Or Target.Value = "" Then Exit Sub
Target.Offset(0, 1).FormulaR1C1 = "=OFFSET(Names!R1C,MATCH(RC[-1],Names,0),0)"
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value
End Sub
Cumalative hours per week.
Paste this formula in J3 and fill down
Formula:
=SUMIF(C:C,"=" &J3,E:E)
Cumalative hours for Q1
Formula:
=sum("K3:K16")
Cumalative hours for Q2
Formula:
=sum("K17:K29")
Cumalative hours for Q3
Formula:
=sum("K30:K42")
Cumalative hours for Q1
Formula:
=sum("K43:K54")
Bookmarks