Hello Smartsheet Community,
I am trying to figure out a formula for when all subtasks (the children) have been checked that it will automatically add a check mark to the parent. Then, when the parent has been checked that it changes the Account Status to "Account Activated." Is that possible?
Hi! I wasn't able to think of a way to do this without converting the sheet to more of a "project plan" where you can edit the project settings and therefore have dependencies automatically tracked.
Caution: I may have done this in a roundabout way! Hope it helps though!
Step 1: I added the columns shown in gray in the screenshot. You don't have to use these columns, and you can always hide the ones you don't end up actually needing, but they will be required in order to enable the Gantt View and the Project Settings.
Step 2: I enabled Gantt View.
Step 3: I clicked on the Project Settings gear (shown with the arrow below). Then I matched each of the fields highlighted below to the new columns I created. You will need to enable dependencies in order for this solution to work.
Step 4: I created a Column Formula in the Subtask Complete checkbox column. The formula I used is shown in the screenshot below. Now, instead of checking the box, you'll just need to indicate that the task is 100% complete. Since we enabled dependencies, when all the children are 100% Complete, the parent will automatically be 100% complete as well, and the checkbox will check! NOTE, if the sub-task is NOT complete, you want to make sure to put 0% or it does mess up the dependency thing. It COULD work better than it does today.
Step 5: The next part of your request can be handled in different ways, but here's what I did. I created a helper column with the following formula in it. The helper column is to help easily identify which rows are parent rows and which are children.
Here's the formula for easy copy and pasting: =IF(COUNT(ANCESTORS([Dealer Name]@row)) = 0, "Yes", IF(COUNT(ANCESTORS([Dealer Name]@row)) > 0, "No", ""))
I made it a column formula. This column is shown in orange in the screenshot below. Note, since it's a helper column, it can also be hidden. I like to personally hide and lock helper columns so I don't confuse others or have someone accidentally overwrite it.
Step 6: Create your workflow! Click on Automation, and then Manage Workflows and then build a workflow like the one below. Remember that for automations to work, you have to save the changes you make on your sheet... and sometimes you may need to refresh your screen, or wait a bit. But it does work :) I tested it out.
Hi @Anthony DAmbrosio - happy to continue helping.
First, I'll say I noticed the formula I provided above for "Is Parent?" only works if it's the eldest parent. Here's a better formula you can use to get ANY parent: =IF(COUNT(CHILDREN([Dealer Name]@row)) > 0, "Yes", IF(COUNT(CHILDREN([Dealer Name]@row)) = 0, "No", "")) ...sorry about that!
Next, to get what you're looking for with "Account Status" - see below.
I found this Help article on Parent Rollup Functionality.
Within the article, see the part I've highlighted below.
Therefore, start and end dates do matter if you want the % Complete to calculate. I tested this out with your data.
Screenshot 1: Before entering dates
Before I entered any dates, you can see that it's calculating the parent to be 0% complete, even though one of the children is complete.
Screenshot 2: After entering dates
After I entered a few dates, I noticed that the % complete did adjust! It looked at the duration to help determine the % complete of the parent. Since the longer duration of the dates was complete, it calculated % complete to be 83%.
The formula I used in the "Account Status" didn't even need to reference the "helper" Is Parent? column. I created that previously because I thought it would be helpful if you just wanted the parent rows automatically updated with a status value.
Here's the formula I have in "Account Status:" =IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started"))
I also used conditional formatting to help the status values stand out more:
Hi! I wasn't able to think of a way to do this without converting the sheet to more of a "project plan" where you can edit the project settings and therefore have dependencies automatically tracked.
Caution: I may have done this in a roundabout way! Hope it helps though!
Step 1: I added the columns shown in gray in the screenshot. You don't have to use these columns, and you can always hide the ones you don't end up actually needing, but they will be required in order to enable the Gantt View and the Project Settings.
Step 2: I enabled Gantt View.
Step 3: I clicked on the Project Settings gear (shown with the arrow below). Then I matched each of the fields highlighted below to the new columns I created. You will need to enable dependencies in order for this solution to work.
Step 4: I created a Column Formula in the Subtask Complete checkbox column. The formula I used is shown in the screenshot below. Now, instead of checking the box, you'll just need to indicate that the task is 100% complete. Since we enabled dependencies, when all the children are 100% Complete, the parent will automatically be 100% complete as well, and the checkbox will check! NOTE, if the sub-task is NOT complete, you want to make sure to put 0% or it does mess up the dependency thing. It COULD work better than it does today.
Step 5: The next part of your request can be handled in different ways, but here's what I did. I created a helper column with the following formula in it. The helper column is to help easily identify which rows are parent rows and which are children.
Here's the formula for easy copy and pasting: =IF(COUNT(ANCESTORS([Dealer Name]@row)) = 0, "Yes", IF(COUNT(ANCESTORS([Dealer Name]@row)) > 0, "No", ""))
I made it a column formula. This column is shown in orange in the screenshot below. Note, since it's a helper column, it can also be hidden. I like to personally hide and lock helper columns so I don't confuse others or have someone accidentally overwrite it.
Step 6: Create your workflow! Click on Automation, and then Manage Workflows and then build a workflow like the one below. Remember that for automations to work, you have to save the changes you make on your sheet... and sometimes you may need to refresh your screen, or wait a bit. But it does work :) I tested it out.
Thank you so much for the help!, It is greatly appreciated.
All the best,
Anthony D'Ambrosio
Hi @MariaCurtis,
Hope you are well and had a good day. I was hoping that you could help me out again. Do you know of a formula that would show the ""status" that just the grand parent" and "parent"? For example, in my screen shot below.
First, when all subtasks under a parent are at 0%, I want each "parent" to show as "Not Started" in the Account Status row, as well as the "grand parent."
Next, when the task owner marks a subtask 100%, I would like for that particular "parent" to show "In Progress" as well as the "grand parent" in the Account Status Row.
Last, when the "parent" turns 100%, I want to show that particular "parent" as "Completed." When all "parents" are shown as "Completed" I would like for the "grand parent" to show "Completed" as well.
Any help would be greatly appreciated.
Hi @Anthony DAmbrosio - happy to continue helping.
First, I'll say I noticed the formula I provided above for "Is Parent?" only works if it's the eldest parent. Here's a better formula you can use to get ANY parent: =IF(COUNT(CHILDREN([Dealer Name]@row)) > 0, "Yes", IF(COUNT(CHILDREN([Dealer Name]@row)) = 0, "No", "")) ...sorry about that!
Next, to get what you're looking for with "Account Status" - see below.
I found this Help article on Parent Rollup Functionality.
Within the article, see the part I've highlighted below.
Therefore, start and end dates do matter if you want the % Complete to calculate. I tested this out with your data.
Screenshot 1: Before entering dates
Before I entered any dates, you can see that it's calculating the parent to be 0% complete, even though one of the children is complete.
Screenshot 2: After entering dates
After I entered a few dates, I noticed that the % complete did adjust! It looked at the duration to help determine the % complete of the parent. Since the longer duration of the dates was complete, it calculated % complete to be 83%.
The formula I used in the "Account Status" didn't even need to reference the "helper" Is Parent? column. I created that previously because I thought it would be helpful if you just wanted the parent rows automatically updated with a status value.
Here's the formula I have in "Account Status:" =IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started"))
I also used conditional formatting to help the status values stand out more:
Good Morning @MariaCurtis ,
Please do not apologize! You have been amazing to work with and you are teaching me a lot! I have one more questions (hopefully :) ).
I created a new column labeled "Stage Name." Is there a formula that can be written to show me what phase a dealer is in based on the Account Status column? Ultimately, I would like to show in card view where a dealer is by Stage Name (see second screen shot).
Again, thank you so much for all your help!
Anthony D'Ambrosio
@Anthony DAmbrosio , this should be achievable (I hope)!
Can you share the criteria? How would the formula know what stage to put each card into? I can see stages somewhat match to the parent task grouping name, but is that all it is?
If your parent task groupings will always contain similar words to the stage name, you could use this formula:
=IF(CONTAINS("Recruitment", [Dealer Name]@row), "Recruitment Phase", IF(CONTAINS("Onboarding", [Dealer Name]@row), "Onboarding Phase", IF(CONTAINS("Dealer Engagement", [Dealer Name]@row), "Dealer Engagement Phase", IF(CONTAINS("Dealer Activation", [Dealer Name]@row), "Dealer Activation Phase", ""))))
Let me know if that doesn't do the trick for you.
