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!
