Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula/Macro
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula/Macro
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula/Macro
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula/Macro
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) "Srikanth" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula in macro | Excel Worksheet Functions | |||
Is there a Formula or Macro for This? | Excel Discussion (Misc queries) | |||
Formula Macro | Excel Discussion (Misc queries) | |||
Macro Formula for Max value | Excel Worksheet Functions | |||
Do I need a formula or Macro? | Excel Worksheet Functions |