Need formula for identifying whether a task is a subtask (regardless of overall task level)
Hello,
I have a project plan that has 6 levels and am looking for a formula that will identify, in a separate text column, whether or not a task is a subtask regardless of what level it is at. (So only that the subtask is a lower level than its parent task and not that it is at a specific level)
As an example, I would need to be able to automatically calculate in a report that there are 7 total subtasks (regardless of what level they are at).
- level 1
- subtask
- subtask
- level 2
- subtask
- subtask
- subtask
- level 3
- subtask
- subtask
Thank you, in advance, for your help!
Answers
-
I do this by creating two columns named ANC and DES. These are helper columns that I use for the Conditions in Conditional Formatting.
For Column ANC, I enter the formula =COUNT(ANCESTORS()) and Convert that to a Column Formula (I also lock the column because I don't like others messing with my settings).
For Column DES, I enter the formula =COUNT(DESCENDANTS()) and convert that to a Column Formula (locking that column as well).
I shrink these down, center the data, and set to Grey background with Grey letters - no one needs to see them, so I hide these two columns as well.
Next is the magic, open Conditional Formatting and select Add New Rule.
Each level will require it's own rule, thus three levels requires three rules.
The first rule is the simplest: if ANC column is = 0, then apply X formatting to the entire row or just to the columns you need highlighted.
The second rule has one more step: if ANC column = 1 AND DES column Is Greater Than 0, then apply X formatting where needed.
The third and subsequent rules are much easier because you can Clone the second rule and just adjust the ANC = 2 and change the Formatting.
If fourth level is needed, then Clone the second rule and update ANC = 3 & update formatting, etc.
You can see where this goes….
I have a screen shot of the rules and the sheet below so you can see:
Hope this helps.
-
Hi,
I hope you're well and safe!
I usually add a helper column called Level with the formula below. You can then use it to identify parents, use it for conditional formatting, and more.=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)
I hope that helps!
Be safe, and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Joe & Andre - thank you both SO MUCH! I appreciate your time and expertise!
Best,
Brian
-
Happy to help!
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Glad we could help and give you options.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!