Summary Sheet [Solved/Closed]

Posts
135
Registration date
Thursday January 21, 2010
Last seen
January 7, 2017
- Mar 12, 2011 at 06:33 AM - Latest reply:
Posts
368
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............
See more 

9 replies

Best answer
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
- Mar 17, 2011 at 06:18 AM
1
Thank you
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

Thank you, RWomanizer 1

Something to say? Add comment

CCM has helped 1631 users this month

Best answer
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
- Mar 18, 2011 at 12:08 AM
1
Thank you
hi GSN,

please refer this uploaded sheet,

http://www.speedyshare.com/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.

Thank you, RWomanizer 1

Something to say? Add comment

CCM has helped 1631 users this month

Posts
2447
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
November 15, 2018
- Mar 14, 2011 at 10:12 AM
0
Thank you
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
135
Registration date
Thursday January 21, 2010
Last seen
January 7, 2017
- Mar 14, 2011 at 01:58 PM
0
Thank you
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
135
Registration date
Thursday January 21, 2010
Last seen
January 7, 2017
- Mar 16, 2011 at 01:44 PM
0
Thank you
Dear Rizvi Sir,

Can you help me out in this ?
Posts
135
Registration date
Thursday January 21, 2010
Last seen
January 7, 2017
- Mar 17, 2011 at 12:43 PM
0
Thank you
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
135
Registration date
Thursday January 21, 2010
Last seen
January 7, 2017
- Mar 17, 2011 at 01:23 PM
0
Thank you
http://www.speedyshare.com/files/27455271/New_Work.xls


Here's the sample file sir..........
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- Mar 17, 2011 at 02:39 PM
where is the formula in the file sir ?
Posts
135
Registration date
Thursday January 21, 2010
Last seen
January 7, 2017
- Mar 17, 2011 at 10:20 PM
Formula....

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