Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to have a formula build a table! (version 2000)

    I have an excel document with multiple worksheets. I want to create a summary worksheet that reflects the current status of all the other worksheets. The idea is to be able to hit a <ctrl> X for example and have the formula read through all the other worksheets and build a table of detail based on certain critieria. To keep it simple, the other worksheets represent individual projects being managed by a PMO. These sheets contain employee info of which End Date for the project is found. I want the formula or function to search all columns representing end date for each project worksheet and when a date is found, return the employee name, end date, resource manager and RM date notified. All the data exist on the same rows as the end date. This returning data is generated into rows on the summary sheet. I certainly hope this is clear, if not let me know.

    Thanks

  2. #2
    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: how to have a formula build a table! (version 2000)

    Might be possible via a Pivot table, though I think it would work better with 1 sheet rather than multiple sheets.

    Barring that, you might need a macro, though you don't give enough details to write one.

    Steve

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

    Re: how to have a formula build a table! (version 2000)

    Could you upload an example of what you are trying to do with dummy data?
    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to have a formula build a table! (version 2000)

    i am not familar with pivot tables so i didn't explore that option. The current excel documnet has 7 project worksheets with each sheet containing the same types of data as follows:

    Resource Role,Resource Name,Start Date,End Date,Extend Date,Reassigned to Project,Reassigned Date,% Avail,Hourly Rate (Cdn$),Hourly Loaded Cost (US$),Recruiting Status(Employ /Cont),Location,Date Resource to be Notified of Release,Date Res Mgr Notified of Release Date,Resource Mgr,Backup Resource,Transition Reference,Training Reqr.

    I want to create an 8th sheet that can produce the following:
    Project Name(this is found in the name of the worksheet being searched)
    Resource Name(from the worksheet details)
    End Date (from the worksheet details)
    Revised End Date (from the worksheet details)
    Resource MAnager (from the worksheet details)
    RM date Notified (from teh worksheet details)

    Criteria is to search each project worksheet looking at the <Date Res Mgr Notified of Release Date> field. If it = "Not Yet" then return all above data to summary sheet and put "Not Yet" in this date field otherwise, use the date found in this date field. See, the local PM's maintain their own worksheet, the summary is to produce a quick snapshot of the resources of all projects. There are two groupings those that have a "Not Yet" in the field <Date Res Mgr Notified of Release Date> and those that have a valid date.

    Thanks

  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: how to have a formula build a table! (version 2000)

    It would be easier to create 1 master sheet with all the similar data. Include a NEW column which contains the info that would be similar to a SHEET name (project name?)

    WIth this setup you can use PIVOT TABLES directly as well as advance filtering, autofiltering with subtotals, Dfunctions, ARRAY formulas, and other techniques much more directly than with separate sheets.

    In my opinion, I can see very few benefits to making 7 identically formatted sheets and then try to combine the summaries. With Autofilter and a column for the "sheet" you could essentially "on the fily" make the one sheet "act like the 7 and you get the benifits of easier manipulation with little or even NO coding requirements.

    Pivot tables are very powerful and require no coding. Having 7 sheets will be more troublesome to handle.

    Steve

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to have a formula build a table! (version 2000)

    I appreciate your insight. From a project perspective they want their own worksheets. Maybe a summary worksheet of all the project worksheets is needed then use the built in optiosn you describe. I will also look into pivot tables using the summary sheet.

    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: how to have a formula build a table! (version 2000)

    As I said, with Autofilter, they could do a filter to ONLY see their items and not worry about the others.

    If you want to continue with multiple sheets, we could help with the macro, but we need more info. providing a sample workbook with several sheets with sample data and the eighth "summary sheet" example, we could provide some help with VB to do what you want.

    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
  •