Solved

Excel - Count tally for multiple sheets

Posted on 2013-05-22
11
447 Views
Last Modified: 2013-06-01
Hi,

I am currently working on an excel spreadsheet for business purposes. I have a set of multiple sheets, each based on a weekly setting having every day of the week listed horizontally. Each sheet also has a set of names listed vertically, with hours "worked" listed horizontally in accordance with the day of the week.

My intention and goal is to make one more sheet that would list all the names listed on all the sheets, with a formula that would "search" for those specific names and do a grand tally count for the total hours. See example below.

Sheet1:
Name:           | Mon   | Tues  | Wed  | Thurs | Fri  |  Sat  | Sun |
John                    8              8            8              8          8      
Jill                                         8            8                                    12        12
Jim                       9                            8                          8        12        12


Sheet2:
]Name:           | Mon   | Tues  | Wed  | Thurs | Fri  |  Sat  | Sun |
John                    8              8            6              8          6      
Jill                                         8            6                                    12        12
Jim                       9                            8                          8        12        12
Jack                     12                         12                                    12


So for example, for my last sheet I would have all 4 people listed as follows. How would I go about making the formula to search the names through the sheets and count the hours horizontally without hard-coding it due to the everchanging names/order for each sheet.
0
Comment
Question by:subzero05
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39186752
Can you upload a file with this. It is a nuisance trying to copy and lay it out in excel.
0
 

Expert Comment

by:JustBeSimple
ID: 39186753
Do it the other way around. List all the name, day, hour in list format. Repeat the name for each day. Then use pivot table. Please look at the attached file.

Hope this works for you.


Thank you.
Cheers
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39187007
JustBeSimple: Did you intend to attach a file?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39187068
Hi  subzero05,

I have created three worksheets within the attached workbook:

[Sheet1] as per your example above.
[Sheet2] again, as above.
[Total] to present the grouped totals of the Named parties within [Sheet1] & [Sheet2].

The Visual Basic for Applications code within the code module for the [Total] worksheet is as follows:

Option Explicit
Private Sub Worksheet_Activate()

' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28135431.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               28135431
' Question Title:   Excel - Count tally for multiple sheets
' Question Asker:   subzero05                                 [ http://www.experts-exchange.com/M_6562415.html ]
' Question Dated:   2013-05-22 at 08:39:52
'
' Expert Comment:   fanpages
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited / N.Lee [ http://NigelLee.info ]
' --------------------------------------------------------------------------------------------------------------

  Dim blnApplication_ScreenUpdating                     As Boolean
  Dim lngErr_Number                                     As Long
  Dim objADODB_Connection                               As Object
  Dim objADODB_Recordset                                As Object
  Dim strSQL                                            As String
  Dim strErr_Description                                As String
  Dim vntSheet                                          As Variant
 
  On Error GoTo Err_Workbook_SheetActivate
 
  blnApplication_ScreenUpdating = Application.ScreenUpdating
 
  Application.ScreenUpdating = False
  
  Set objADODB_Connection = CreateObject("ADODB.Connection")
 
  objADODB_Connection.Provider = "Microsoft." & IIf(Val(Application.Version) <= 11#, "Jet.OLEDB.4.0", "ACE.OLEDB.12.0")
  objADODB_Connection.ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=Excel " & _
                                          IIf(Val(Application.Version) <= 11#, "8", "12") & ".0;"
  objADODB_Connection.Open
 
  strSQL = ""
 
  For Each vntSheet In Array("Sheet1", "Sheet2")        ' *** Note: Add other worksheets to this Array if required
  
      If Len(Trim$(strSQL)) > 0 Then
         strSQL = strSQL & " UNION "
      End If ' If Len(Trim$(strSQL)) > 0 Then
      
      strSQL = strSQL & "SELECT "
      strSQL = strSQL & "[Name:],"
      strSQL = strSQL & "[Mon],"
      strSQL = strSQL & "[Tues],"
      strSQL = strSQL & "[Wed],"
      strSQL = strSQL & "[Thurs],"
      strSQL = strSQL & "[Fri],"
      strSQL = strSQL & "[Sat],"
      strSQL = strSQL & "[Sun] "
         
      strSQL = strSQL & "FROM "
      strSQL = strSQL & "[EXCEL " & _
                         IIf(Val(Application.Version) <= 11#, "8", "12") & ".0;"                                                                            ' Note: Val(...) only recognizes a period ["."] as a valid decimal separator
      strSQL = strSQL & "IMEX=1;"
      strSQL = strSQL & "HDR=Yes;"
      strSQL = strSQL & "DATABASE=" & ActiveWorkbook.FullName & "].[" & CStr(vntSheet) & "$]"
      
  Next vntSheet
  
  If Len(Trim$(strSQL)) > 0 Then
     strSQL = "SELECT [Name:], Sum([Mon]), Sum([Tues]), Sum([Wed]), Sum([Thurs]), Sum([Fri]), Sum([Sat]), Sum([Sun]) FROM (" & strSQL & ")"
     strSQL = strSQL & " GROUP BY"
     strSQL = strSQL & "[Name:]"
     
     Set objADODB_Recordset = CreateObject("ADODB.Recordset")
 
     objADODB_Recordset.CursorType = 3                                                                                                                      ' adOpenStatic
     objADODB_Recordset.CursorLocation = 3                                                                                                                  ' adUseClient
     objADODB_Recordset.ActiveConnection = objADODB_Connection
 
     objADODB_Recordset.Open (strSQL)
 
     Cells.ClearContents
     Worksheets("Sheet1").Rows(1&).Copy Me.Rows(1&)
     Me.[A2].CopyFromRecordset objADODB_Recordset
     [A1].Select
  End If ' If Len(Trim$(strSQL)) > 0 Then
  
Exit_Workbook_SheetActivate:

  On Error Resume Next
 
  If Not (objADODB_Recordset Is Nothing) Then
     objADODB_Recordset.Close
     Set objADODB_Recordset = Nothing
  End If
 
  If Not (objADODB_Connection Is Nothing) Then
     objADODB_Connection.Close
     Set objADODB_Connection = Nothing
  End If

  Application.ScreenUpdating = blnApplication_ScreenUpdating
  
  Exit Sub
 
Err_Workbook_SheetActivate:

  lngErr_Number = Err.Number
  strErr_Description = Err.Description
  
  On Error Resume Next
  
  Application.ScreenUpdating = True
  
  MsgBox "Error #" & CStr(lngErr_Number) & _
          vbCrLf & vbLf & _
          strErr_Description, _
          vbExclamation Or vbOKOnly, _
          ActiveWorkbook.Name
         
  Resume Exit_Workbook_SheetActivate
 
End Sub

Open in new window



Please download/save this workbook locally (instead of simply opening the file from your web browser's cache).

If you wish to make changes to the contents of [Sheet1] &/or [Sheet2] please do so.

Selecting (activating) the [Total] worksheet will collate the data from those two worksheets & present grouped totals as you required.

PS. It should not be necessary to save the workbook before viewing the [Total] worksheet, but if you find the totals are not correct, I would recommend saving the workbook in advance of doing this.

BFN,

fp.
Q-28135431.xls
0
 
LVL 3

Accepted Solution

by:
InfoStranger earned 500 total points
ID: 39191641
How about the sumif formula?  I show in my example that even the summary does not need to be in any particular order.  The names need to be unique or else you will add them together as well.

The sumif function looks at a range of values that meet your criteria then adds from a range of values that match what your criteria is.  In your case, the criteria is the person's name.  If the name does not exist, a value of 0 is entered.  So, if you use sumif for both worksheets as shown below, you will add the values found for the name and adding the values.

=SUMIF(Sheet1!$A:$A,Total!$A2,Sheet1!B:B)+SUMIF(Sheet2!$A:$A,Total!$A2,Sheet2!B:B)
Example-Summary.xlsx
0
 

Author Closing Comment

by:subzero05
ID: 39210085
I'm not entirely sure how this works yet but it sure does look like what I need. Thanks.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39210095
Thanks for acknowledging my input.

You're welcome.
0
 

Author Comment

by:subzero05
ID: 39210171
fanpages,

I took a look at your file and was unable to determine what you did until I checked your code. It is exactly what I need, but I have no idea how to implement it to my file. I have 10-12 sheets, and the hour cells are in different locations.

 Can you explain the coding behind the sheet? Thanks in advance.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39210186
fanpages,

I took a look at your file and was unable to determine what you did until I checked your code. It is exactly what I need, but I have no idea how to implement it to my file. I have 10-12 sheets, and the hour cells are in different locations.

Can you explain the coding behind the sheet? Thanks in advance.

Please advise on these differences & I may look at making the necessary changes.

Alternatively, please provide a cut-down version of the workbook (or the 10-12 worksheets) with all sensitive data removed/obfuscated so the code can be developed directly within that source file.

Sorry, I have no idea how much experience of Microsoft Visual Basic for Applications you possess.

Explaining the code could be a very lengthy process.
0
 

Author Comment

by:subzero05
ID: 39210220
fanpages,

My knowledge in VB is very limited. If you could please, see if you could implement the code you had on your example worksheet into my example file.

Thank you.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39212550
I presume you will be attaching your sample workbook in a later comment.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question