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

## 7 responses

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

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
```
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,

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.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
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 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.

Didn't find the answer you are looking for?

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

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 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..........
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 17, 2011 at 02:39 PM
where is the formula in the file sir ?
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............ :)