Summary Sheet [Solved/Closed]

Report
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
-
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
-
Hello Sir,

I Have 30 Data Sheets Of Individual Date & 1 Summary Sheet.

The Example Of Those 30 Sheets Are:

A1------------B1------------------------C1----------------------D1-------------E1---------------F1------
S.No-----Employee Code--------Employee Name--------Time In-----Time Out-------OverTime
1-----------KSF-0011------------------Farhan----------------9:30---------19:30------------2-------
2-----------KSF-0012------------------Irfan-------------------9:30---------18:30------------1-------


I Wanna Call All OverTime Of All Sheets To Summary Sheet In front Of There Name Like:

Example Of Summary Sheet:

A1------------A2----------------------A3-------------------------A4
S.No-----Employee Code-------Employee Name-------OverTime
1------------KSF-0011--------------Farhan-------------------2
2------------KSF-0012--------------Irfan----------------------1



I Want The Formula To Look At The Employee Code Of Summary Sheet To All Other Sheets In Column B, If Matched Then Show The Sum Of All Sheets Of That Employee OverTime To Summary Sheet.

Thanks In Advance............

7 replies

Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Hi GSN,

you can collect all sheet data in a single sheet (named Master) by the below code:

after that you can you pivot table for summary.

 
    Sub CopyFromWorksheets()
    Dim wrk As Workbook
    Dim sht, trg As Worksheet
    Dim rng As Range
    Dim colCount As Integer
     
    Set wrk = ActiveWorkbook
     
    For Each sht In wrk.Worksheets
        If sht.Name = "Master" Then
            MsgBox "There is a worksheet called as 'Master'." & vbCrLf & _
            "Please remove or rename this worksheet since 'Master' would be" & _
            "the name of the result worksheet of this process.", vbOKOnly + vbExclamation, "Error"
            Exit Sub
        End If
    Next sht
     
    Application.ScreenUpdating = False
     
    Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count))
    trg.Name = "Master"
    Set sht = wrk.Worksheets(1)
    colCount = sht.Cells(1, 255).End(xlToLeft).Column
    With trg.Cells(1, 1).Resize(1, colCount)
        .Value = sht.Cells(1, 1).Resize(1, colCount).Value
        .Font.Bold = True
    End With
         
    For Each sht In wrk.Worksheets
        If sht.Index = wrk.Worksheets.Count Then
            Exit For
        End If
        Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount))
        trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
    Next sht
     
    Application.ScreenUpdating = True
End Sub
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
hi GSN,

please refer this uploaded sheet,

https://authentification.site/files/27464873/New_Work.xls

Format your date wise sheets as given in the file (first row is header and data start from first column), do not put gap in last row and total row its looks not good.

Run the macro you got a master sheet. you got complete data in Master sheet.(it is very useful if you have 30 sheet as you said earlier) and

now put employee code in summary sheet and drag the formula of c and d column to last row you got the desired result.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
Hi Game Start Now,

You will need to combine SUM and VLOOKUP like this:

      =SUM(VLOOKUP(B2,Sheet1!$B$2:$F$3,5),VLOOKUP(B2,Sheet2!$B$2:$F$3,5),VLOOKUP(B2,Sheet3!$B$2:$F$3,5),VLOOKUP(B2,Sheet4!$B$2:$F$3,5),VLOOKUP(B2,Sheet5!$B$2:$F$3,5),VLOOKUP(B2,Sheet6!$B$2:$F$3,5),VLOOKUP(B2,Sheet7!$B$2:$F$3,5),VLOOKUP(B2,Sheet8!$B$2:$F$3,5),VLOOKUP(B2,Sheet9!$B$2:$F$3,5))

Change the sheet names, change the matrix range and expand formula to 30 sheets. Then drag the formula down for the other employees.

Best regards,
Trowa
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6
Thanks For Replying Sir.

Thanks Sir But If I I Change The Sheets Name To Sheet1 & Sheet2 & So On, Then This Below Formula Also Works The Same With Less Coding.

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:30"))&"'!$C:$C"),A1,INDIRECT("'Sheet"&ROW(INDIRECT("1:30"))&"'!$E:$E")))


But My Junior Colleagues Provide Me The Sheet With Different Names Every time.

So I Thought Macro Will Help Me Out Making Summary Instead Of Changing The Sheets Name All The Time.

Except Summary Sheet Formula Collect & Sum Of That Individual Employee To Summary Sheet.


Thanks In Advance
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6
Dear Rizvi Sir,

Can you help me out in this ?
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6
Thanks for replying sir.

It only copy column A to master sheet but i want the formula to first look at the Summary Sheet Column B. If Column B1 is Blank then do nothing if B1 is Suppose "Naeem" Or Any Number Like "KSF-1010" then look at that word or number to all other sheets in column B, if It Matched then sum the column D from that sheet. and then same to all sheets. if suppose KSF-1010 is found in sheet 1 and sheet2 in Column B then sum his overtime in column D in Summary Sheet.

Means I Want To Collect & Sum His Overtime from all sheets in Column D if the code or the name from column b from summary sheet is matched to Othersheets.................
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6
https://authentification.site/files/27455271/New_Work.xls


Here's the sample file sir..........
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
where is the formula in the file sir ?
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
6
Formula....

Sir Formula Is On Summary Sheet On Overtime Column............ :)