Today I did a presentation at the ILF Annual Conference. It was about using Excel to track Reference statistics (and getting rid of the traditional clipboard). Attendees asked for the code to be made available online so they could copy and paste it rather than retyping all of it and risking mistyping one symbol and having it not work.

So here is the code. Right off my handouts. I welcome any questions you may have.

Reference Macro
Application.ScreenUpdating = False
Worksheets("Transactions").Activate
ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = Date
ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate
ActiveCell.FormulaR1C1 = Time
ActiveCell.Value = ActiveCell.Value - Int(ActiveCell.Value)
ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate
ActiveCell.FormulaR1C1 = "1"
Application.CutCopyMode = False
ActiveSheet.Range("a1").Select
Worksheets("Main").Activate
response = MsgBox("Reference Transaction Logged", 64, "Thank you")
ActiveWorkbook.Save
Application.ScreenUpdating = True

General Macro
Application.ScreenUpdating = False
Worksheets("Transactions").Activate
ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = Date
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.FormulaR1C1 = Time
ActiveCell.Value = ActiveCell.Value - Int(ActiveCell.Value)
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.FormulaR1C1 = "1"
Application.CutCopyMode = False
ActiveSheet.Range("A1").Select
Worksheets("General").Activate
ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = Date
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.FormulaR1C1 = "1"
Application.CutCopyMode = False
ActiveSheet.Range("A1").Select
Worksheets("MAIN").Activate
Response = MsgBox("Email Reference Transaction Logged", 64, "Thank you")
ActiveWorkbook.Save
Application.ScreenUpdating = True

Data Collection Code for 8:00 hour, first column
=SUM(IF(INDIRECT("'"&$H$1&"'!$B$3"):INDIRECT("'"&$H$1&"'!$B$"&COUNTA(INDIRECT("'"&$H$1&"'!$A:$A")))>=0.333, IF(INDIRECT("'"&$H$1&"'!$B$3"):INDIRECT("'"&$H$1&"'!$B$"&COUNTA(INDIRECT("'"&$H$1&"'!$A:$A")))<0.375, IF(INDIRECT("'"&$H$1&"'!C$3"):INDIRECT("'"&$H$1&"'!C$"&COUNTA(INDIRECT("'"&$H$1&"'!$A:$A")))=1,1,0))))

Data Collection Code for 9:00 hour, first column
=SUM(IF(INDIRECT("'"&$H$1&"'!$B$3"):INDIRECT("'"&$H$1&"'!$B$"&COUNTA(INDIRECT("'"&$H$1&"'!$A:$A")))>=0.375, IF(INDIRECT("'"&$H$1&"'!$B$3"):INDIRECT("'"&$H$1&"'!$B$"&COUNTA(INDIRECT("'"&$H$1&"'!$A:$A")))<0.416, IF(INDIRECT("'"&$H$1&"'!C$3"):INDIRECT("'"&$H$1&"'!C$"&COUNTA(INDIRECT("'"&$H$1&"'!$A:$A")))=1,1,0))))

Data Collection Code for 8:00 hour, second column
=SUM(IF(INDIRECT("'"&$H$1&"'!$B$3"):INDIRECT("'"&$H$1&"'!$B$"&COUNTA(INDIRECT("'"&$H$1&"'!$A:$A")))>=0.333, IF(INDIRECT("'"&$H$1&"'!$B$3"):INDIRECT("'"&$H$1&"'!$B$"&COUNTA(INDIRECT("'"&$H$1&"'!$A:$A")))<0.375, IF(INDIRECT("'"&$H$1&"'!D$3"):INDIRECT("'"&$H$1&"'!D$"&COUNTA(INDIRECT("'"&$H$1&"'!$A:$A")))=1,1,0))))

Averages Code for 8:00 hour, first column
=Totals!B3/IF(OR(LEFT($H$1,2)="01",LEFT($H$1,2)="03",LEFT($H$1,2)="05",LEFT($H$1,2)="07",LEFT($H$1,2)="08",LEFT($H$1,2)="10",LEFT($H$1,2)="12"),31,IF(LEFT($H$1,2)="02",28,30))

Averages Code for 9:00 hour, first column
=Totals!B4/IF(OR(LEFT($H$1,2)="01",LEFT($H$1,2)="03",LEFT($H$1,2)="05",LEFT($H$1,2)="07",LEFT($H$1,2)="08",LEFT($H$1,2)="10",LEFT($H$1,2)="12"),31,IF(LEFT($H$1,2)="02",28,30))

Averages Code for 8:00 hour, second column
=Totals!C3/IF(OR(LEFT($H$1,2)="01",LEFT($H$1,2)="03",LEFT($H$1,2)="05",LEFT($H$1,2)="07",LEFT($H$1,2)="08",LEFT($H$1,2)="10",LEFT($H$1,2)="12"),31,IF(LEFT($H$1,2)="02",28,30))

Formula for Title of Totals Page (cell A1)
="TOTAL REFERENCE TRANSACTIONS for "&TEXT(H1,"mmm yyyy")