How should I set up my sheet or sheets to create sub-tasks based on Request type column?

emoser1978

I currently have the subtasks (Provisioning, Deployment and ROPE) as separate columns within the main sheet created from a form however it isn't clicking in my head how this would work in this one sheet. Should this be setup in a separate sheet?

I also want to set up a trigger in automation, as an example when Provisioning is checked off as complete, it will alert another person to complete deployment and finally the Rope and so on to the next Request type and their tasks.

I don't want to add separate rows within this main sheet to create the task list, the look of this main sheet should show one line only per new hire.

Please let me know if you need anything more from me. Thank you.

Brian_Richardson

It really depends on what you want to do.

If you want to track all the completions on one line in the master sheet, you can certainly do that without trying to create new lines. You could set yourself up with a "Task 1", "Task 2", "Task 3" column and populate those columns with formulas based on the Request Type.

So for example, Task 1 column would look like this:

= IF( [Request Type]@row="IT Equipment Procurement","Provisioning", IF([Request Type]@row="Access/Permissions","Badge",IF([Request Type]@row="IT Orientation Scheduling","New Hires",IF([Request Type]@row="Operational Training Scheduling","NextGen",""))))

Then have a Task 1 Complete checkbox for people to check off.

Your alerts and update request automations can then be setup for each Task Level. For example, the automation for Task 1 would trigger when Task 1 is not blank (which will happen when someone picks a Request Type) and can fire out the Update Request to include the Task 1 description and the checkbox for completion.

The automation for Task 2 would then trigger when the completion checkbox for Task 1 is checked, and so on down the line.

emoser1978

i will try that out now, thank you.

emoser1978

I was able to get it to work however, there are occasions where a person can request multiple Request types in the Form. Is this still possible to keep on one sheet if the formula changes to a =COUNTM with the =IF formula?

emoser1978

or maybe a =IF(HAS… formula?

Brian_Richardson

Yep you have the right idea: IF(HAS(

Or you might think about forcing people to split rows of there are multiple types, otherwise it’s going to be tough to understand the sequence of tasks with mixed tasks.