How do you auto-populate subtasks based on a dropdown selection?

mariahneu

Hi!

I created a project intake form in Smartsheet and the form results appear in a Project Intake Sheet. I have a dropdown column called Effort Type. When someone selects whether the project is a Lean effort, a DMAIC effort or a Kaizen effort, I want subtasks to auto-populate for that row in the same sheet. The subtasks will be a list of tools associated with each effort. The list of tools is different for each effort. I have a list of all of the tools (I can put the list of tools alongside the effort it pairs with in a different sheet in Smartsheet if the formula or workflow need a list to pull from).

Examples of tools: Define and Measure Audit Analysis Plan, Financial Analysis, Fishbone Diagram

DMAIC efforts have 55 tools, so the subtask lists for each form entry could be quite long - this is why we are using Smartsheet (so we can track all of this in one card per form submission vs. all of the processes we have to have in place to do this manually in Excel).

I also want to auto-assign a deadline to each subtask (this column would be called Desired End Date). I need the formula or workflow to look at the date in the Start Date column for the main task and then add a certain number of days onto the Desired End Date for each subtask to schedule each tool's due date (i.e. take 5/22/21, add 14 days to it, and enter that value into Desired End Date for the task).

This would mean that, say, the Financial Analysis subtask for a DMAIC effort is due 30 days after the start date for the main task.

Please see below for a snippet of the sheet. The second row is blank because it's a subtask test. Thank you so much!

Julio S.

Hi @mariahneu, 

Since each Effort type would include a specific recurring structure with their own relevant tools, have you considered creating different sheet templates for each Effort type? With these, users could select the appropriate template to track their project based on the effort type and all data to create new sheets based on each template for each Effort type. If the information of each different project needs to be consolidated into a single view, this can be achieved by creating a Report that scopes all projects and Effort types. 

Alternatively, for a more automated way of creating projects with specific defined structure based on project types, the Smartsheet Control Center could also be an option. Note, however, that this is Premium add-on that may not be available in your plan.

 Regarding automatically assigning a finish date based on the start date of a project, this can be controlled with the "Duration" column that you can set when activating dependencies in your projects. 

I hope this can offer some guidance on possible options to accomplish what you intend. 

Cheers! 

Julio