Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jun 2004
    Location
    Whistler, Br. Columbia, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summarize jobs from many tabs into one list

    I have a workbook with many tabs, one for each room/area of a hotel (102, 103, 104, Library, Kitchen, etc). On each of these tabs is a list of jobs pertaining to that room. Some rooms have many jobs, some very few. This list is constantly changing as jobs are added and completed (jobs are added as they arise, and as they are done columns F&G (Done By and Date Completed) are filled in.

    I would like to have a summary tab that will list all jobs remaining to be completed. Ideally, column A of the summary would be the source tab name (room number/name) and columns B-E would show columns B-E (job details) from that source tab. I don't know how many rows will be on each tab as it will change regularly. Rows that contain a value in column G (Date Completed) don't need to be displayed in the summary (perhaps it would be easier to bring ALL rows from each tab that contain data, and then filter them on the summary...)

    I suspect that additional tabs may be added in the future but it should not be a regular occurrence, so I can manually add to the summary sheet to check those tabs if needed.

    Any ideas?

  2. #2
    Lounger
    Join Date
    Jun 2004
    Location
    Whistler, Br. Columbia, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    found some code here (http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx) that I'm going to try and use

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    JonTom:

    Attached is a non VBA alternative. It is based on the use of simple formulas on each Tab Sheet. The example was set up for Rows 10 to 40, but that can be expanded to any length. By Creating a Pivot Table using multiple sources and selecting the columns with the formulas the Pivot Table will have enough data to create the attached Table. Any change to the Tabs will be reflected on the Pivot Table after it is refreshed.

    TomD
    Attached Files Attached Files

  4. #4
    New Lounger
    Join Date
    Feb 2012
    Location
    St. Louis, MO
    Posts
    21
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Hello,

    I have attached a spreadsheet that I think achieves the results you would like. A little preface before you open it. The spreadsheet contains a macro that runs when you select the first worksheet (summary). This macro is located in Module1 and sorts the list by Room # ascending and Task # descending. You will need to enable macros/content for this worksheet to perform correctly.

    Also, on the first worksheet (summary) there seem to be a lot of blank cells but in truth the last row to contain data is row 61. Every row above 62 contains a formula. I have marked the first blank row with a background of red.

    There are comments in row 2 of each column that give an explanation of the formulas and how they work.

    For right now the Summary worksheet is set up to pull in data from worksheets Room102, Room 103 and Room 104 up to 20 rows.

    Please let me know if this achieves the results you were looking for.

    Thanks,

    RVASQUEZ
    Attached Files Attached Files

Posting Permissions

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