+ Reply to Thread
Results 1 to 6 of 6

Autopopulate Master table from multiple tables which show project no, staff name&hrs workd

  1. #1
    Registered User
    Join Date
    10-30-2013
    Location
    London, UK
    MS-Off Ver
    Excel 2007
    Posts
    6

    Autopopulate Master table from multiple tables which show project no, staff name&hrs workd

    Hi,

    I have created 10 separate worksheet for project managers to manage staff resourcing.

    However, now I need to feed back all this information into a MASTER Workload sheet in a separate workbook which will allow management to discuss staff workload in weekly meetings.
    Therefore I need the info in the separate workbooks to feed back into the master workbook in the form of:
    Project No. | Staff name | Hrs assigned week1 | week2 | etc.
    There will be duplicates of names (column 1), however be next to difference project numbers (column 2), and vice versa.

    I've done INDEX and MATCH in the worksheets, but now I need to do something similar to get a range which will populate the cells under the relevant date columns.

    Please tell me there's a way of doing this so it automatically feeds back instead of copy and pasting.

    The file is also attached.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Autopopulate Master table from multiple tables which show project no, staff name&hrs w

    Hi and welcome to the forum

    Im not really sure what you want here Could you provide a few sample answers (entered manually if necessary), so we can see what you want?

    Also, on a side note, it will probably be easier to pull data if you were using real dates, instead of text looking like dates. If for instance in OUTPUT H4, you entered 23/9/13 instead of 23.09.2013, then in I4, you could use this =H4+7, copied across
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-30-2013
    Location
    London, UK
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Autopopulate Master table from multiple tables which show project no, staff name&hrs w

    Hi!

    Thanks for helping.

    I've correcting the dates as you've suggested.

    What I'd like if possible is shown on the last tab (I've only manually entered it as I don't know how to get it to work).
    I'd like (not necessarily in this order):
    Column A to list Staff names
    Column B to be corresponding Project Numbers for hours worked
    Column C to be project activity
    Column D to whenever necessary to be the hours worked for that project under the weekly dates.

    I now also need to get a line graph showing the project stages (x=dates, y=workload), and another line graph showing forecasting/ prospect and target fee (x=time, y= workload).

    Is any of this possible?
    Do I need to change the original layout?
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Autopopulate Master table from multiple tables which show project no, staff name&hrs w

    Thanks for the file. I can (kinda) see what you want, but I dont see where all the info is coming from. For instance 121111 appears ONLY on the "outgoing link" worksheet.

    Can you talk me through - step-by-step if necessary - what you are trying to do...what comes from where, what goes where, why etc?

  5. #5
    Registered User
    Join Date
    10-30-2013
    Location
    London, UK
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Autopopulate Master table from multiple tables which show project no, staff name&hrs w

    Hi,

    Sorry that might be a typo.

    I have a master workbook where staff names, levels, charge out rates, costs and project numbers and titles are added. These feed into the current workbook I uploaded before through the 'INCOMING LINKED TO MASTER' sheet.
    In the 'INPUT Project Workload' sheet the managers should be able to select which project they are in charge of, assign staff and under the date and project stage assign hours or the staff to work.
    The sheet should also be able to calculate how much is being spent on the project, show if its profitable and let the manager keep an eye on what to invoice and when.

    Hopefully this is what is already set up.

    This information then needs to be sent back to a 'MASTER' workbook from all the managers workbooks (approx. 10) so our Director can then asses how company work load is, if people are over worked/ over assigned to projects, and allow him to see and forecast workload, projects and incoming invoices.

    For this I need to show what staff is working on which project, the time, stage and weeks assigned.
    So in the end you'll be able to select any member of staff and it'll show how many projects they are assigned to - to see if they are over capacity, over the weeks. Or select any project and see if it needs resources, or is overspending.

    Also I need to be able to graph the results to;
    -forecast workload,
    -projected incoming payment forecasting to show if we're going to have a slow period,
    -another to show what projects are active, which quotes/ future projects we are expected to win and show a target line - encase we need to hire more staff for the demand; and,
    -project stages over time

    The graphs will be used in fortnightly management meetings with the Director and the managers.

    Does this make sense?

  6. #6
    Registered User
    Join Date
    10-30-2013
    Location
    London, UK
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Autopopulate Master table from multiple tables which show project no, staff name&hrs w

    Hi FDibbins.
    Is my description ok?
    Is it possible?
    ????

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. I need to copy the information of several tables to a master table
    By JCM_28 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-19-2013, 12:59 PM
  2. [SOLVED] Creating a Master table from several smaller tables
    By brharrii in forum Excel General
    Replies: 14
    Last Post: 07-19-2013, 10:19 AM
  3. Multiple Pivot Tables from Master Pivot Table and Print as PDF - Need advise
    By vishnu01445 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-27-2013, 02:07 PM
  4. Autopopulate Different Sheets from a master sheet
    By JamesFletcher in forum Excel General
    Replies: 7
    Last Post: 05-07-2013, 03:12 AM
  5. [SOLVED] Trying to create table/chart to show where staff is located along
    By Soraiya in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-07-2005, 10:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1