Need a formula/Macro

[Srikanth]

Hi All,

I'm trying to locate a formula where I can make my work easy.
The questions is: I've 15 sheets(1 sheet for each employee) in a workbook,
where we store the data of alloted work and in them we got 12 variety of
issues. So what I'm trying to do is - count/Sum the action and the date i.e,
how many persons have did how many varieties on that particular date. I have
one master where am I'm gonna insert this formula.


Example:
Action Taken Date
Wrong Image 26/04/2008
Wrong Image 26/04/2008
Wrong Image 26/04/2008
Wrong Image 27/04/2008
Wrong Image 27/04/2008
Missing Pages 27/04/2008
Missing Pages 27/04/2008
Missing Pages 28/04/2008
Missing Pages 28/04/2008
Missing Pages 28/04/2008
Missing Pages 28/04/2008
Missing Pages 28/04/2008



DATE Wrong Image Missing Pages Foreign Language Total
Apr-01 =sum of all employees data for this particular issue 0 0 0
Apr-02 0 0 0 0
Apr-03 0 0 0 0
Apr-04 0 0 0 0
Apr-05 0 0 0 0
Apr-06 0 0 0 0
Apr-07 0 0 0 0
Apr-08 0 0 0 0
Apr-09 0 0 0 0
Apr-10 0 0 0 0
Apr-11 0 0 0 0
Apr-12 0 0 0 0
Apr-13 0 0 0 0
Apr-14 0 0 0 0
Apr-15 0 0 0 0
Apr-16 0 0 0 0
Apr-17 0 0 0 0
Apr-18 0 0 0 0
Apr-19 0 0 0 0
Apr-20 0 0 0 0
Apr-21 0 0 0 0
Apr-22 0 0 0 0
Apr-23 0 0 0 0
Apr-24 0 0 0 0
Apr-25 0 0 0 0
Apr-26 12 0 0 0
Apr-27 0 0 0 0
Apr-28 0 0 0 0
Apr-29 0 0 0 0
Apr-30 0 0 0 0

 

[Joel]

The code assumes your total are on a sheet called master. The code will look
at all the other sheets and add up the totals

On the Master worksheet add a formula in the total column that will add up
the other columns like
=sum(b5:d5)
adding up row 5 numbers. Then copy this formula to all the other rows.


Sub get_totals()
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Master" Then
RowCount = 2
Do While sht.Range("A" & RowCount) <> ""
Action = sht.Range("A" & RowCount)
ADate = sht.Range("B" & RowCount)
With Sheets("Master")
MRowCount = 2
Do While .Range("A" & MRowCount) <> ""
If .Range("A" & MRowCount) = ADate Then

Select Case Action

Case "Wrong Image"
.Range("B" & MRowCount) = .Range("B" & MRowCount) + 1
Case "Missing Pages"
.Range("C" & MRowCount) = .Range("C" & MRowCount) + 1
Case "Foreign Language"
.Range("D" & MRowCount) = .Range("D" & MRowCount) + 1
End Select
Exit Do
End If
MRowCount = MRowCount + 1
Loop
End With

RowCount = RowCount + 1
Loop
End If
Next sht
End Sub

 

[Srikanth]

Code seems correct, but its not working after modifications:

The modified is :

Sub get_totals()
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Master" Then
RowCount = 2
Do While sht.Range("A" & RowCount) <> ""
Action = sht.Range("A" & RowCount)
Date = sht.Range("B" & RowCount)
With Sheets("Master")
MRowCount = 2
Do While .Range("A" & MRowCount) <> ""
If .Range("A" & MRowCount) = ADate Then

Select Case Action

Case "Agmt not found in Dox"
.Range("B" & MRowCount) = .Range("B" & MRowCount) + 1
Case "Agreement Enriched"
.Range("C" & MRowCount) = .Range("C" & MRowCount) + 1
Case "Escalated to SME"
.Range("D" & MRowCount) = .Range("D" & MRowCount) + 1
Case "Existing Attribute Incorrect"
.Range("E" & MRowCount) = .Range("E" & MRowCount) + 1
Case "Foreign Language"
.Range("F" & MRowCount) = .Range("F" & MRowCount) + 1
Case "Forward to SME Review"
.Range("G" & MRowCount) = .Range("G" & MRowCount) + 1
Case "Forward to SME Review"
.Range("G" & MRowCount) = .Range("B" & MRowCount) + 1
Case "Image not Accessible"
.Range("H" & MRowCount) = .Range("H" & MRowCount) + 1
Case "Missing Pages"
.Range("I" & MRowCount) = .Range("I" & MRowCount) + 1
Case "Need not Enrich"
.Range("J" & MRowCount) = .Range("J" & MRowCount) + 1
Case "No Image Found"
.Range("K" & MRowCount) = .Range("K" & MRowCount) + 1
Case "Work in Progress"
.Range("L" & MRowCount) = .Range("L" & MRowCount) + 1
Case "Wrong Image"
.Range("M" & MRowCount) = .Range("M" & MRowCount) + 1
Case "Unable to Save"
.Range("N" & MRowCount) = .Range("N" & MRowCount) + 1
End Select
Exit Do
End If
MRowCount = MRowCount + 1
Loop
End With

RowCount = RowCount + 1
Loop
End If
Next sht
End Sub

 

[Joel]

I found one mistake in you changes. Date is a variable in excel which is
today date which is the date setting on your computer.. I had to use a
different name for a variable so I chose ADate. Your code attempted to
change the date on your computer. the code didn't work becaquse ADate never
got set (it was empty) and tried to compare the empty variable against the
dates in column A in your worksheet master. Make the following change.

from
Date = sht.Range("B" & RowCount)
to
ADate = sht.Range("B" & RowCount)