How to get a summarized sheet of all the data sheets of a month

[Kindle]

In our company we enter data in excel sheet for each day with each employees timing to come at office and the time they leave from office with overtime of each day. So at the end of month I have 30 sheets and now I want it to summarize it into one sheet, first take a look at table

A1------------B1------------------------C1----------------------D1-------------E1---------------F1------
S.No-----Employee_Code-------- Employee_Code --------Time_In-----Time_Out-----Over_Time
1----------- Johnason ------------------ UTV-0051----------------9:00---------19:00--------------2-----
2----------- David ------------------ UTV-0052-------------------9:00---------18:00------------1-------
And I want it to summarize it as
A1------------A2----------------------A3-------------------------A4
S.No----- Employee_Name ------- Employee_Code -------Over_Time
1------------ Johnason -------------- UTV-0051-------------------2
2------------ David -------------- UTV-0052----------------------1

So, I guys can you help me out to find or provide me the formula which first look at the Employee_Code in summarized sheet and then will search for the same Employee_Code in daily sheet if the code matched then in summarized sheet over_time of daily sheet will be added for the particular Employee_Code. Hope I clearly explained you the problem definition, Please help me.

[DotNetUser]

Yes I understood your problem and I have solution for you, As you said You need summarized the so sheet data in one sheet in which three columns will be same and you just want to change the overtime i.e you want to add all overtime of all the employees for a month in summarized sheet. For that you have to combine

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

Here you just have to change the matrix and sheet name and expand the above given formula to 30 sheets and then drag down the formula for other employees one by one.

[Kindle]

Thanks a lot for your concern and for the solution but sorry I found some problem in solution because I have to change sheet name but I use ‘Sheet’ only then I can do it with less coding I guess

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

And these every day sheet have different name everyday because it’s submitted by different-different employees that’s why I thought that changing the sheet name again and again will be very tedious work so I thought it’s better to use Macro. Please share your suggestion, Thank you.

[Gunilla]

Hey i have one solution for you with the help of which you can collect your all sheets information into one Master Sheet and after using the code for Master sheet you can pivot table for the summary which you were looking for,

Sub CopyFromWorksheets()
    Dim wrkbuk As Workbook
    Dim sht, trgt As Worksheet
    Dim rang As Range
    Dim TotalcolCount As Integer
     
    Set wrkbuk = ActiveWorkbook
     
    For Each sht In wrkbuk.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 trgt = wrkbuk.Worksheets.Add(After:=wrkbuk.Worksheets(wrkbuk.Worksheets.Count))
    trgt.Name = "Master"
    Set sht = wrkbuk.Worksheets(1)
    TotalcolCount = sht.Cells(1, 255).End(xlToLeft).Column
    With trgt.Cells(1, 1).Resize(1, TotalcolCount)
        .Value = sht.Cells(1, 1).Resize(1, TotalcolCount).Value
        .Font.Bold = True
    End With
         
    For Each sht In wrkbuk.Worksheets
        If sht.Index = wrkbuk.Worksheets.Count Then
            Exit For
        End If
        Set rang = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, TotalcolCount))
        trgt.Cells(65536, 1).End(xlUp).Offset(1).Resize(rang.Rows.Count, rang.Columns.Count).Value = rang.Value
    Next sht
     
    Application.ScreenUpdating = True
End Sub

[Kindle]

That’s so nice of you guys, without knowing me you guys are putting so much effort thanks a lot again, but above code doesn’t give me the proper Master sheet, This code only copies column A to the master sheet and I was looking for something else. I want this formula to first look into summary sheet in column C if the a cell for a employee is blank then do nothing and if there is some code for an employee then look for the same code in same column in other sheets and it it found in other sheet too then add the over time of all the entries for that code and put it into column D in summary table in front of particular employee.

[Gunilla]

Ok I see I don’t know what’s going wrong in your case it worked for me may due to the format of your Excel sheet and format of the date it’s not working. This can be the case because as you said you got daily sheets from different-different employees so they may be different date format and mostly while creating a Excel sheet people leaves some blank rows and columns which doesn’t look good. First use same format for the date and put all the sheets in same format first row for header and data should start from first column. And now you run the Macro you might get right thing this time i.e Master sheet. And in summary sheet put the employee code and now drag that formula in B and D column to the last row and this time you will get the result you were looking for.