Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Location
    Dover, New Jersey, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create a List to Sum Data across worksheets?

    I have an Excel 2000 file for employee time cards. Column A is a list of project names worked on during the week by an employee, and Col's B-H (Sun. - Sat.) are the hours worked on the project that particular day. Each employee has their own worksheet with this format in the workbook. Is there a way for me to make a list (on a new worksheet) of all unique projects worked-on in the week, and then sum all the employee-hours worked for each of those projects?

    Thanks for any help.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a List to Sum Data across worksheets?

    Is the project list the same for all employees, is the list fairly stable or does in vary frequently, and how often are there changes in employees? If the project list is the same for each employee (with nothing filled in for projects that the employee didn't work on), and if the list is stable, and you don't have a lot of changes of employees, they you can probably just come up with a summary sheet that uses the Sum function to add up the times across sheets. If any of those conditions are not met, they you are probably going to have to write VBA code to loop through the employee sheets and create the summary.
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Apr 2003
    Location
    Dover, New Jersey, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a List to Sum Data across worksheets?

    The project list varies from week to week, and is different for each employee. The employee list is very stable. I kind of thought this would require some coding.

    Any suggestions for how I can teach myself VBA coding in Excel. (And just what does the A stand for in VBA?)

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Create a List to Sum Data across worksheets?

    Is it possible that you could have all employees on one sheet. This would make summarisation by Pivot Table quiet easy.

    It can still be done using different sheets, but might require some manual copying and pasting each week.

    Andrew C

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a List to Sum Data across worksheets?

    VBA is Visual Basic for Applications, so the A is for Applications.

    There are a lot of places that teach Excel VBA courses like community colleges, CompUSA, etc. There are also a lot of Excel and VBA books available (I personally don't find most of the books to be particularly good), or you can just jump in and start trying to do something that needs programming like your current project. Places like this forum are really good to get help.

    If you want, we can try to help you through this project. So far, it sounds like a fairly simple project. If so, we are going to need a lot of additional information to even get started. Here are some questions to get us through the first few lines of code:

    1- What is the name of the summary sheet (the name on the tab)?

    2- What is this summary sheet going to look like. It sounds like two columns with a label in the top cell of each column. The first column would contain project names, and the second column would contain the total time worked on the project by all employees. Is this correct?

    3- Is there something that the program can use to recognize a worksheet as one of the employee time sheets? Do the sheet names contain something like the first two characters of the sheet name are E-, or is there something in a cell on these sheets that is always the same on all sheets, and that would not appear in the same cell on any other sheet? The program is going to need some way of recognizing the employee sheet, so if there isn't something now that can be used, you are going to have to come up with something.
    Legare Coleman

  6. #6
    New Lounger
    Join Date
    Apr 2003
    Location
    Dover, New Jersey, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a List to Sum Data across worksheets?

    No, each employee needs their own sheet for privacy reasons.

  7. #7
    New Lounger
    Join Date
    Apr 2003
    Location
    Dover, New Jersey, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a List to Sum Data across worksheets?

    1. There is no summary sheet currently, so lets just call it Summary.

    2. You're exactly right.

    3. Each sheet is named by the employee's "Last Name, First Name" on the tab (e.g. Smith, Bob). There is also a cell in each sheet with the employee's full name (e.g. Bob Smith). If there is a better way to identify each employee sheet, I can easily add it since this doesn't have any required formats to follow.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a List to Sum Data across worksheets?

    OK, based on what you have told me, the code below will build the summary sheet like I described in an earlier message. Nothing you gave me in your last message could be used to identify which sheets are employee time sheets, and which are other things. If I used employee name, I would have to have a list of employee names to compare against the sheet names, and that would be a bit slower. So, what I have done is say that the time sheet names would be named E-lastname, firstname. If you would rather use something else, then the code can be modified. If you want to begin to learn VBA programming, then one place to start would be to understand what this code does. You can study it and look things up in the help files, and we will help with anything you still don't understand.

    <pre>Public Sub BuildSummary()
    Dim wsCSheet As Worksheet, wsSum As Worksheet
    Dim I As Integer, J As Integer
    Dim dTotHours As Double
    Dim sWk As String
    Set wsSum = Worksheets("Summary")
    wsSum.Range("A:A", "1:1").ClearContents
    wsSum.Range("A1").Value = "Project"
    wsSum.Range("B1").Value = "Hours"
    For Each wsCSheet In Worksheets
    If Left(wsCSheet.Name, 2) = "E-" Then
    I = 0
    Do While wsCSheet.Range("A2").Offset(I, 0) <> ""
    dTotHours = 0
    For J = 1 To 7
    If IsNumeric(wsCSheet.Range("A2").Offset(I, J)) Then
    dTotHours = dTotHours + wsCSheet.Range("A2").Offset(I, J)
    End If
    Next J
    J = 0
    Do While wsSum.Range("A2").Offset(J, 0) <> ""
    If wsSum.Range("A2").Offset(J, 0).Value = wsCSheet.Range("A2").Offset(I, 0).Value Then
    wsSum.Range("A2").Offset(J, 1).Value = wsSum.Range("A2").Offset(J, 1).Value + dTotHours
    Exit Do
    End If
    J = J + 1
    Loop
    If wsSum.Range("A2").Offset(J, 1).Value = "" Then
    wsSum.Range("A2").Offset(J, 0).Value = wsCSheet.Range("A2").Offset(I, 0).Value
    wsSum.Range("A2").Offset(J, 1).Value = dTotHours
    End If
    I = I + 1
    Loop
    End If
    Next wsCSheet
    wsSum.Columns("A:B").AutoFit
    Set wsSum = Nothing
    Set wsCSheet = Nothing
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •