Using dropdowns to copy cells from one worksheet to corresponding cells of another worksheet and add new rows if needed.

  • I’ve tried looking through past threads for a similar issue but I can't find anything that matches my problem. I’m pretty much a novice when it comes to excel so I was hoping someone could help me out. I’ve tried to outline my goals and background info for the workbook as best I can and I’ve attached the workbook to make it easier to understand what I’m trying to accomplish. Also, if anyone knows of an easier way to organize the workbook, I’m open to recommendations.



    Background:


    I’m a plumbing engineer working in the construction industry. I design the plumbing systems for different types of buildings. Over the course of the design of a building, I have a million tasks that I need to complete and items that I need to coordinate with other engineering disciplines. Keeping track of all these tasks and coordination items during design can be hard and sometimes overwhelming. As a result, tasks can fall through the cracks and then cause expensive issues to solve during construction. I’m trying to make a workbook that will allow me to select common or specific tasks from database worksheets and add them to “tracker” worksheets. I want the workbook to be sort of a working template that I can update as I encounter projects that have new/specific tasks. Then save-as the template to make project trackers for all the different projects I work on to keep track of unresolved items.



    Goals:


    Worksheets “SD Tasks”, “DD Tasks”, and “CD Tasks” are the databases worksheets I mentioned. I’ve added a yes/no dropdown to the column “add to project” in these worksheets.


    When I set the “add to project” dropdown to “yes” in the SD Tasks, DD Tasks, and CD Tasks worksheets, I want the cells from the “coordination tasks” and “discipline” column to be added to the “task” and “discipline” column in the “Coordination Tracker” worksheet.


    The “coordination tracker” worksheet is set up, so SD Coordination Items, DD Coordination Items, and CD Coordination Items are in their own block of rows. So, when the tasks are added from the database worksheets, I want them to be propagated in the corresponding sections in the “Coordination Tracker” Worksheet. Additionally, if all the blank rows in the SD Coordination Items or DD coordination Items sections are already filled with tasks I want a new row to be created within the section where the new task will be added.


    I need the same thing to happen for the “engineering task” column of the SD Tasks, DD Tasks, and CD Tasks worksheets except I want the engineering tasks to propagate the “Task Tracker” worksheet.


    I also want to make sure that the tasks from the SD Tasks, DD Tasks, and CD Tasks worksheets are not deleted from their respective worksheet when the tasks are added to the tracker worksheets



    Thanks in advance for the help!


    Project Tracker -Test.xlsx

  • Welcome to Ozgrid.


    I think you are approaching this the wrong way. The dropdowns should be based on the task in the Coordination sheet. Take a look at this and let me know if it is what you want. I've added drop downs to the SD tasks

  • Roy,


    Thank you I appreciate your help. I had initially thought about doing it this way to keep things simple. My only reservation about this approach is that it could be a little bit tedious scrolling through the drop-down for each line in the coordination tasks worksheet. I like the idea of being able to scroll through the SD Tasks worksheet and select the tasks the pertain to the project. This way I only have to scroll through the tasks once and I don't accidentally create duplicate tasks in the coordination task worksheet.


    Thanks,