Summary Sheet Solved/Closed

Question

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............

TrowaD · Answer

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

Game Start Now · Answer

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

Game Start Now · Answer

Dear Rizvi Sir,

Can you help me out in this ?

RWomanizer · Answer

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

Game Start Now · Answer

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.................

Game Start Now · Answer

https://authentification.site/files/27455271/New_Work.xls


Here's the sample file sir..........

RWomanizer · Answer

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.

Summary Sheet

7 responses

Subscribe To Our Newsletter!