Help Keeping a running tally

[mcal45usn]

Hello,

I have a workbook with 31 sheets (1 for each day of a month) then I have one sheet listing on A3:A161 names of specific individuals.

On each day, we recieve call outs (not coming in) from some of those individuals. I then record their name in that days sheet depending on order of call in A6:A44 (we usually get no more than 10 a day).

What im trying to do is, everytime I enter a specific name on the days sheet, i want a column where it is an updated tally of how many times they have already called in.

Example, if chris calls out on the 1st, 6th and 10th, When I enter his name on hte 10th, I want the next column to say "3" and then so on.

Is there anyway to do this?

[mancubus]

welcome to the forum.

yes.

upload a file as explained in my signature which will demonsrate the desired output.

names are recorded in A3:A161 or A6:A44?

Show us!

[mcal45usn]

welcome to the forum.

yes.

upload a file as explained in my signature which will demonsrate the desired output.

names are recorded in A3:A161 or A6:A44?

Show us!


17478

[Paul_Hossler]

Maybe something like this in the Workbook module (see attachment)

Note that Day 5 had 2 spaces in it

Basically, what I think you wanted was if you enter a Name on "Day X" sheet in Col A, then you want the count of that name from "Day 1" to Day X-1" of that name entered in Col B by that name on the "Day X" sheet




Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rABS As Range
Dim sName As String
Dim dCount As Long, iWS As Long, iToday As Long
Dim ws As Worksheet

If Sh.Name = "Individuals" Then Exit Sub
If Intersect(Target, Sh.Range("A:A")) Is Nothing Then Exit Sub

sName = Target.Cells(1, 1).Value
If Len(sName) = 0 Then ' cleared the name cell
Target.Cells(1, 2).ClearContents
Exit Sub
End If

iToday = InStr(Sh.Name, " ")
iToday = CLng(Right(Sh.Name, Len(Sh.Name) - iToday))

dCount = 1

Application.ScreenUpdating = False
For iWS = 1 To iToday - 1
Set ws = Worksheets("Day " & Format(iWS, "##")) ' Day 5 had extra space
Set rABS = Range(ws.Cells(6, 1), ws.Cells(ws.Rows.Count, 1).End(xlUp))

dCount = dCount + Application.WorksheetFunction.CountIf(rABS, sName)
Next iWS

Application.EnableEvents = False
Target.Cells(1, 2).Value = dCount
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

[Bob Phillips]

Why don't you just put all of the call entries on one sheet with the date of the entry and then create a nicer, more sophisticated dashboard showing any metrics you want? You can have name dropdowns, other paremeters, and it will be so much easier to manage. Keep the data simple, make the analysis smart.

[mcal45usn]

Perfect, Thank you.