This is very helpful, thank you! I have modified to allow for different date criteria, and a test of whether the item in the "Tasks" row is called "Test (Decision)": =IF(COUNT(CHILDREN(Tasks@row)) > 0, IF(COUNTIFS(DESCENDANTS([End Date]@row), >=TODAY(), DESCENDANTS([End Date]@row), 0, 1, 0), IF(AND([End Date]@row >= TODAY(), [End Date]@row 0, IF(COUNTIFS(DESCENDANTS([End Date]@row), >=TODAY(), DESCENDANTS([End Date]@row), 0, 1, 0), IF(AND([End Date]@row >= TODAY(), [End Date]@row
You'll need to replace the "Tasks@row" in the first contains with @cell... =IF(COUNT(CHILDREN(Tasks@row)) > 0, IF(COUNTIFS(DESCENDANTS([End Date]@row), >=TODAY(), DESCENDANTS([End Date]@row), 0, 1, 0), IF(AND([End Date]@row >= TODAY(), [End Date]@row
@@workflowcreative Wonderful, that worked! Thank you for the quick reply and tip about your website. I am sure I will be reaching out for more help. Thanks!
Brilliant! Lack of parent-child heirarchy is one of the most baffling omissions in terms of report functionality. Thank you so much for sharing this workaround. Subscribed!
This is great. Love it. I have 2 Qs and it will be great if you could answer those. Q1. How did you add arrow image in your formula for Task Name Hierarchy? Q2. In the Overdue formula, if I want to two statuses (Not Started and In Progress), how do I modify the formula? In my project plan, I have 4 statuses - Not Started, In Progress, On Hold and Completed.
@@workflowcreative Thanks. I am using your formula right now. I changed Start Date to End Date and Not Started to In Progress. However I do want to capture tasks that are not started and overdue. =IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIFS(DESCENDANTS([End Date]@row), 0, 1, 0), IF(AND([End Date]@row < TODAY(), Status@row = "In Progress"), 1, 0))
@@vikramsharma9892 I added it in two places. Just delete the one you don't need... =IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIFS(DESCENDANTS([Start Date]@row), 0, 1, 0), IF(AND([Start Date]@row < TODAY(), OR(Status@row = "Not Started", Status@row = "In Progress")), 1, 0))
Thanks for your video. So helpful in building a report that shows the parent/child hierarchy!! I do have a challenge I am trying to figure out. I have a sheet that is a "process decomposition" for accounting processes with 8 levels. Rows may or may not contain information in multiple columns including: procedures, position responsible, reference guides and forms. Do you have a suggestion for a formula in a helper column to create a report that shows rows with procedures "not blank" and the parent row(s) above it? (I can duplicate that for other columns.) Thanks for your help! Hopefully Smartsheet will soon include parent row options in reports.
Hi Terri! It's going to be a combo of an If statement and an ancestor function. Hard to say without seeing your data though. I saw you booked some time on my calendar for next week! I look forward to chatting and helping you with your formula. - Ryan
This works great. I'm trying to mimic this for current signed in user so that I can create a single report where a current user can view all tasks assigned to them across multiple project plans. The problem is that when I apply filter 'Assigned to' I lose the parent/child organization and only the task which is assigned to the user is displayed in the report. Any workaround that you can recommend?
Hi Darius. This is tough b/c you may have multiple people assigned to the tasks beneath the parent and reports do not play well with Multi-Contact columns. Otherwise, I would say have the parent collect the Assigned_To children. If they are all the same person, it would work. Alternatively, you can add a column that displays the parent on the same row as the child. It's not ideal, but it will would add some context to the task row for the viewer. - Ryan
What filters did you set in your report to have them show as you have on the video? I have input all of the formulas correctly but am struggling to get the right filtering set up.
@@workflowcreative The filters show the initial filters - were there other filters you used once you created the hidden columns for the parent tasks? Using the initial filter still doesn't show the parent tasks with the new formulas. TIA for the help!
I have added all of the formulas successfully but I cannot seem to see it working on my report, I wonder if there is something I am missing on the Filters?
Hi! Have to added all the proper columns? Is it bringing in any rows into your sheet? Maybe remove your filters one by one and see if that works. -Ryan
@@christenuber5508 Go back through the video and be really meticulous. It's usually something small that's missing (like you're referencing a specific cell instead of a range, etc.). Otherwise, if you want to send me an email at Ryan@WorkflowCreative.com with some screenshots/video of your Smartsheet components, I can take a look for you. - Ryan
![[TH] VALORANT Champions Seoul - Grand Final - EDG vs TH](http://i.ytimg.com/vi/WvObMc9VelI/mqdefault.jpg)
This is EXACTLY what I was missing. Thank you very much for doing this video. Loved it and I'm using it today!
You are so welcome! Glad it helped! - Ryan
This is very helpful, thank you!
I have modified to allow for different date criteria, and a test of whether the item in the "Tasks" row is called "Test (Decision)":
=IF(COUNT(CHILDREN(Tasks@row)) > 0, IF(COUNTIFS(DESCENDANTS([End Date]@row), >=TODAY(), DESCENDANTS([End Date]@row), 0, 1, 0), IF(AND([End Date]@row >= TODAY(), [End Date]@row 0, IF(COUNTIFS(DESCENDANTS([End Date]@row), >=TODAY(), DESCENDANTS([End Date]@row), 0, 1, 0), IF(AND([End Date]@row >= TODAY(), [End Date]@row
You'll need to replace the "Tasks@row" in the first contains with @cell...
=IF(COUNT(CHILDREN(Tasks@row)) > 0, IF(COUNTIFS(DESCENDANTS([End Date]@row), >=TODAY(), DESCENDANTS([End Date]@row), 0, 1, 0), IF(AND([End Date]@row >= TODAY(), [End Date]@row
@@workflowcreative Wonderful, that worked! Thank you for the quick reply and tip about your website. I am sure I will be reaching out for more help. Thanks!
@@EmilyWyent Glad to hear it! - Ryan
Level Column: =COUNT(ANCESTORS([Task Name]@row)) + IF(COUNT(CHILDREN([Task Name]@row)) > 0, "+", "")
Task Name Hierarchy Column: =IF(CONTAINS(0, Level@row), [Task Name]@row, IF(CONTAINS(1, Level@row), "⬇ " + [Task Name]@row, IF(CONTAINS("2+", Level@row), "↘ " + [Task Name]@row, IF(CONTAINS(2, Level@row), " - - " + [Task Name]@row, IF(CONTAINS(3, Level@row), " - - - " + [Task Name]@row)))))
Overdue Column: =IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIFS(DESCENDANTS([Start Date]@row), 0, 1, 0), IF(AND([Start Date]@row < TODAY(), Status@row = "Not Started"), 1, 0))
Remember: change your column names as appropriate! Enjoy! WorkflowCreative.com for more Smartsheet Tips
Brilliant! Lack of parent-child heirarchy is one of the most baffling omissions in terms of report functionality. Thank you so much for sharing this workaround. Subscribed!
100% agree! And you're welcome! :) Happy to help. - Ryan
This is great. Love it. I have 2 Qs and it will be great if you could answer those.
Q1. How did you add arrow image in your formula for Task Name Hierarchy?
Q2. In the Overdue formula, if I want to two statuses (Not Started and In Progress), how do I modify the formula? In my project plan, I have 4 statuses - Not Started, In Progress, On Hold and Completed.
1) Emojis!!
2) What is your current formula?
-Ryan
@@workflowcreative Thanks. I am using your formula right now. I changed Start Date to End Date and Not Started to In Progress. However I do want to capture tasks that are not started and overdue.
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIFS(DESCENDANTS([End Date]@row), 0, 1, 0), IF(AND([End Date]@row < TODAY(), Status@row = "In Progress"), 1, 0))
@@vikramsharma9892 I added it in two places. Just delete the one you don't need... =IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIFS(DESCENDANTS([Start Date]@row), 0, 1, 0), IF(AND([Start Date]@row < TODAY(), OR(Status@row = "Not Started", Status@row = "In Progress")), 1, 0))
@@workflowcreative This is perfect. Thank you very much for doing this. Love it.. and I am using it..
Hello sir, I reallly appreciate your efforts and this is one of the best video I found on Smartsheet.
Glad I could help Deepanshu. I appreciate your comment. Sometimes it feels like I’m shouting in the void. :)
Thanks for your video. So helpful in building a report that shows the parent/child hierarchy!! I do have a challenge I am trying to figure out. I have a sheet that is a "process decomposition" for accounting processes with 8 levels. Rows may or may not contain information in multiple columns including: procedures, position responsible, reference guides and forms. Do you have a suggestion for a formula in a helper column to create a report that shows rows with procedures "not blank" and the parent row(s) above it? (I can duplicate that for other columns.) Thanks for your help! Hopefully Smartsheet will soon include parent row options in reports.
Hi Terri! It's going to be a combo of an If statement and an ancestor function. Hard to say without seeing your data though. I saw you booked some time on my calendar for next week! I look forward to chatting and helping you with your formula. - Ryan
Thank you! I look forward to chatting.
This works great. I'm trying to mimic this for current signed in user so that I can create a single report where a current user can view all tasks assigned to them across multiple project plans. The problem is that when I apply filter 'Assigned to' I lose the parent/child organization and only the task which is assigned to the user is displayed in the report. Any workaround that you can recommend?
Hi Darius. This is tough b/c you may have multiple people assigned to the tasks beneath the parent and reports do not play well with Multi-Contact columns. Otherwise, I would say have the parent collect the Assigned_To children. If they are all the same person, it would work. Alternatively, you can add a column that displays the parent on the same row as the child. It's not ideal, but it will would add some context to the task row for the viewer. - Ryan
What filters did you set in your report to have them show as you have on the video? I have input all of the formulas correctly but am struggling to get the right filtering set up.
Hi There. About a minute into the video, I display the filters. These may not work for you though if your data is different - Ryan
What filters did you use to get your report to show up this way? All my formulas worked just having trouble with the filtering.
Hi There. About a minute into the video, I display the filters. These may not work for you though if your data is different - Ryan
@@workflowcreative The filters show the initial filters - were there other filters you used once you created the hidden columns for the parent tasks? Using the initial filter still doesn't show the parent tasks with the new formulas. TIA for the help!
@@LyliGarcia-qu9qc Try removing all of your filters and see if you get the rows you want. Then slowly add filters to your report. - Ryan
Super useful
Thank you! Glad it helped. - Ryan
I have added all of the formulas successfully but I cannot seem to see it working on my report, I wonder if there is something I am missing on the Filters?
Hi! Have to added all the proper columns? Is it bringing in any rows into your sheet? Maybe remove your filters one by one and see if that works. -Ryan
@@workflowcreative I am having the same issues and don’t have any filters on my sheet. The formula shows up in the cell but the result does not appear
@@christenuber5508 Go back through the video and be really meticulous. It's usually something small that's missing (like you're referencing a specific cell instead of a range, etc.). Otherwise, if you want to send me an email at Ryan@WorkflowCreative.com with some screenshots/video of your Smartsheet components, I can take a look for you. - Ryan