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

How to get a summarized sheet of all the data sheets of a month
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.
Reply With Quote
#2
Old 04-04-2011
DotNetUser DotNetUser is offline
Member

Join Date: Nov 2008
Posts: 1,187
Re: How to get a summarized sheet of all the data sheets of a month
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
Code:

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.
Reply With Quote
#3
Old 04-04-2011
Kindle Kindle is offline
Member

Join Date: Mar 2011
Posts: 55
Re: How to get a summarized sheet of all the data sheets of a month
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

Code:

=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.
Reply With Quote
#4
Old 04-04-2011
Gunilla's Avatar
Gunilla Gunilla is offline
Member

Join Date: May 2008
Posts: 982
Re: How to get a summarized sheet of all the data sheets of a month
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,
Code:

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

Reply With Quote
#5
Old 04-04-2011
Kindle Kindle is offline
Member

Join Date: Mar 2011
Posts: 55
Re: How to get a summarized sheet of all the data sheets of a month
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.
Reply With Quote
#6
Old 05-04-2011
Gunilla's Avatar
Gunilla Gunilla is offline
Member

Join Date: May 2008
Posts: 982
Re: How to get a summarized sheet of all the data sheets of a month
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.