Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Combining several worksheets (Excel 2002/2003)

    This might be something easy, but I just cannot think of that easy solution.

    We have a workbook that has weekly worksheets with lists of all the work time reported by each employee. The worksheets are combined into a total worksheet with the totals for each employee for the period. We would like to give the employees a report of their own weekly information with the totals. The individual employee would be listed on each worksheet in the same row.

    Is there an easy way to get the weekly information for either all employees or an individual on a separate worksheet?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining several worksheets (Excel 2002/2003)

    I think we'll need to have more detailed information, preferably in the form of a workbook with samples of the sheets involved and an indication of the desired result.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Combining several worksheets (Excel 2002/2003)

    I suspect a pivot table will do what you desire, though as Hans indicates we would need more info from you if you want any detailed answers

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Combining several worksheets (Excel 2002/2003)

    i created a sample document, which contains the first worksheet example of possible results.

    There are 2 weeks in this sample but there could be upto 17 weeks. My cheat way of creating the example was to copy the data from each worksheet into Sheet 1, then sort by employee ID and date, and insert totals. It can be done that way, but there are actually 200 employees and usually only 5 to 10 are needed for review.
    Attached Files Attached Files

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Combining several worksheets (Excel 2002/2003)

    You can create a pivot table based on the combined sheet (I added another set to it) to get what is on the "Pivot" Sheet.

    You can select teh "MembeID" list in the pivot table and select/unselect the ones to have display only the ones you want.

    A better scheme would be to not have 17 different identically-formatted sheets (I always find this setup inefficient and more trouble than its worth) but to rearrange you data into the large table right away and only keep one table of all the data. Filtering can be used to view a selected week or to see an individual's info and also pivot tables to view certain summaries

    Ideally, a better way may be (since most of the numbers are zero) is to reformat the data completely and have only the columns:
    EndDate
    MemberID
    Type (a selection from C13:N13)
    Hours

    Although perhaps better since you separate out "Benefits":
    EndDate
    MemberID
    Hour Type (reg or Benefit)
    Type (Reg is C13:J13, Benefit is K13:N13)
    Hours

    Steve
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Combining several worksheets (Excel 2002/2003)

    Thanks for the work on the pivot table, Steve.

    I am stuck with the weekly worksheets. The data is coming to the worksheets from a SQL database. The managers compare the information to a weekly report generated from the database software. We would like to create a cube of the database information, but that will not happen soon.

    Is it possible to create the pivot table using the multiple worksheets?

    There are at least 12 columns of calculations that were not part of the sample, but are used by the managers for weekly adjustments. Is there a way to combined the data on the 17 worksheets, so that the pivot table can be run? We do not need the calculated fields, but probably need most of the fields that were in the sample.

    Thanks, Steve

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Combining several worksheets (Excel 2002/2003)

    You can create a pivot table from a consolidated range of each of the "week" sheets, but it will not be what you want. The consolicated range pivot table is very limited. It will have consolidate as row field the first column of each of the sheets (column A, the enddates) which is fine and create as the "column Fields" the data from the combined headers of all the sheets.

    The problem is that the memberID will be a "column field" and will sum, which by itself is not the issue, but it can not be used as a second row field, which is what you need.

    But for combining the sheet, does this code do what you want? It combines all the sheets that start with "week" and creates a pivottable from the combined data...

    Steve
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Combining several worksheets (Excel 2002/2003)

    Thanks, Steve.

    I think that we can use the code. I see in the code where I can change the starting row, but is there somewhere that I can say to start with column E?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Combining several worksheets (Excel 2002/2003)

    I did not make it general in this way. There are places when working with wks where I have something like ".Cells(lRowStart, 1)" in which the 1 could be changed to a "5" or better yet having a variable (like iColStart) which can be set to 5. I would lean towards creating a variable iColEnd for the last column to import as well. Then use iCols = iColEnd-iColStart +1 as the total columns. Thus you could use iColStart and End for the individual wks sheets and 1 and iCols for the wCombine.

    I will leave it to you (as an exercise) to try and make the changes yourself and test it in your code. I think you will learn more doing this than having me make the changes. I am anal so I would do it with a copy, but the code does nothing to the originals and any changes to the workbook is done on 2 newly created sheets whenever the code is run (so if you test you will keep creating new sheets in the workbook)....

    If you run into any problems, post back some specifics and we can try to help....

    Steve

  10. #10
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Combining several worksheets (Excel 2002/2003)

    Thanks, Steve.

    I think that I can get the variable added and set to my preferred column to start and the number of columns to include. If I have any issues, i will post at that time.

    Thanks again.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Combining several worksheets (Excel 2002/2003)

    Good, I think you will learn from the task and that is one of our goals here, to not only help but also to educate. Think of the addage of teaching a man to fish... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Steve

Posting Permissions

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