Summary Sheet

Solved/Closed
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 - Mar 12, 2011 at 06:33 AM
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 - Mar 18, 2011 at 12:08 AM
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

RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Mar 17, 2011 at 06:18 AM
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
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Mar 18, 2011 at 12:08 AM
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
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Mar 14, 2011 at 10:12 AM
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
0
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Mar 14, 2011 at 01:58 PM
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
0

Didn't find the answer you are looking for?

Ask a question
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Mar 16, 2011 at 01:44 PM
Dear Rizvi Sir,

Can you help me out in this ?
0
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Mar 17, 2011 at 12:43 PM
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.................
0
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Mar 17, 2011 at 01:23 PM
https://authentification.site/files/27455271/New_Work.xls


Here's the sample file sir..........
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Mar 17, 2011 at 02:39 PM
where is the formula in the file sir ?
0
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Mar 17, 2011 at 10:20 PM
Formula....

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