+ Reply to Thread
Results 1 to 6 of 6

Is it possible to tie "static" manually inputted data to a dynamic project list?

  1. #1
    Registered User
    Join Date
    04-28-2021
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    3

    Question Is it possible to tie "static" manually inputted data to a dynamic project list?

    Hello everyone,

    I am working on a manpower lookahead sheet for my company. The goal of this sheet is to allow project managers and project supervisors to input their desired crew size for a project based on a monthly timeline. The sheet will then compare the total crew required crew size for each month (based on a sum of all the projects' required crew sizes) and tell the user if more manpower is required for each month.

    We already have a static version of this tool, but we're trying to make it more dynamic. Ideally, we want the project list to be dynamically populated by linking to an external CSV report that will be updated on a daily, weekly, or monthly basis. The problem I am trying to solve is:

    How do I allow the users to input static data (crew size for each month) while still having that data follow its corresponding project if the dynamic list changes? Is this something that is possible with Excel alone? Will I need to learn Access to use a combination of a database and an Excel sheet? Is there another way I should be doing this that makes more sense?

    I have attached an example of what I am trying to achieve. Basically, I want the numbers on the right to be attached to the same project on the left, so even if the project changes rows, the data from the right will change rows to match the same project. If a project disappears from the report, I would prefer if the data on the right is cleared (for that specific project).

    If I can clarify anything further, please let me know.
    Attached Files Attached Files

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Is it possible to tie "static" manually inputted data to a dynamic project list?

    One way to do this is to use one sheet for data entry, and show the current state of the data on another sheet. The Current sheet would pull the job names out the csv file, and then use VLOOKUP to get the crew requirements from the data entry sheet. They could both be the same format.

    The ideal solution is to integrate your solution with this csv file, instead of making them two separate things, which would be more work to create but not as klunky to use.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    04-28-2021
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Is it possible to tie "static" manually inputted data to a dynamic project list?

    I thought about using that VLOOKUP method, but if I'm understanding you correctly it would still be unable to both show the crew size requirements and allow editing of them on that same sheet. Sort of like a read/write function. I am not sure this is even possible in Excel, I may be expecting too much.

    On the data entry sheet, if the job list changes in the CSV, wouldn't the data entered by the PMs be orphaned?

    I could use the VLOOKUP method as a summary view I guess. Like you said, the users would input data on the entry sheet, but then they could have more features to help sort the data like slicers, etc. on the summary view sheet with the VLOOKUPs. I will experiment with this, thanks!

    EDIT: Could I somehow store the manually inputted data upon opening the Excel file, then reconcile that with the job name/job number from the updated CSV?
    Last edited by jwfox92; 04-29-2021 at 12:06 PM.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Is it possible to tie "static" manually inputted data to a dynamic project list?

    Quote Originally Posted by jwfox92 View Post
    I'm understanding you correctly it would still be unable to both show the crew size requirements and allow editing of them on that same sheet.
    Yes, that's true. One sheet just for editing, the other just for looking.

    I think you are going to need a macro solution. What is the format of the csv value?

  5. #5
    Registered User
    Join Date
    04-28-2021
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Is it possible to tie "static" manually inputted data to a dynamic project list?

    The CSV file just has 5 columns of data: Job Number, Job Name, Total Hours, Hours Used, and Remaining Hours. For this exercise I am not using the final 3 columns yet, just the first 2 (Job Number & Job Name).

  6. #6
    Registered User
    Join Date
    04-19-2021
    Location
    Dublin, Ireland
    MS-Off Ver
    O365, Win10 and Mac
    Posts
    47

    Re: Is it possible to tie "static" manually inputted data to a dynamic project list?

    If you don't want to invest in project planning software (although that may well save you a lot of time) then as suggested by 6StringJazzer the way to go about this is with sheets for editing and another for summarising. You would need a table for resources - number of people available per month, a table for listing your projects with project id, name and dates (imported from CSV) and another table listing the monthly requirement per project. Then you would have a pivot table to join everything together for display purposes. I attach a crude sample. Pivot tables are not my forte. To handle input to the tables I have written a system called Dexel Form. If you download and install that from dexelform.com then you will have the data entry forms for each of the tables.
    Attached Files Attached Files

+ 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. [SOLVED] Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"
    By skydivetom in forum Excel Charting & Pivots
    Replies: 26
    Last Post: 06-27-2020, 11:03 AM
  2. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  3. [SOLVED] Dynamic Dependent "Data Validation -List"
    By ManiThani in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-27-2017, 02:25 AM
  4. [SOLVED] Can Range("xxx") be dynamic instead of static valuel?
    By BOYAR in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-25-2016, 09:55 AM
  5. Replies: 1
    Last Post: 03-09-2016, 12:17 PM
  6. Replies: 8
    Last Post: 02-11-2013, 03:53 AM
  7. [SOLVED] Manually fill up 1 or (some) cells with just "BFW"or"BF" in data validation
    By robke1960 in forum Non English Excel
    Replies: 2
    Last Post: 12-11-2012, 11:31 AM

Tags for this Thread

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