Solved

# Excel - Count tally for multiple sheets

Posted on 2013-05-22
405 Views
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
Question by:subzero05

LVL 43

Expert Comment

Can you upload a file with this. It is a nuisance trying to copy and lay it out in excel.
0

Expert Comment

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

JustBeSimple: Did you intend to attach a file?
0

LVL 35

Expert Comment

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

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;"

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:]"

Cells.ClearContents
Worksheets("Sheet1").Rows(1&).Copy Me.Rows(1&)
[A1].Select
End If ' If Len(Trim\$(strSQL)) > 0 Then

Exit_Workbook_SheetActivate:

On Error Resume Next

If Not (objADODB_Recordset Is Nothing) Then
End If

If Not (objADODB_Connection Is Nothing) Then
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
``````

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

InfoStranger earned 500 total points
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

I'm not entirely sure how this works yet but it sure does look like what I need. Thanks.
0

LVL 35

Expert Comment

Thanks for acknowledging my input.

You're welcome.
0

Author Comment

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

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

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

I presume you will be attaching your sample workbook in a later comment.
0

## Featured Post

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…