Show child tasks and their status based on due date with parent project reference

Options
kellysjostrom
kellysjostrom ✭✭
edited 04/15/22 in Formulas and Functions

Hi everyone! I am trying to create a dashboard for each of my project managers. I am hoping to have a rollup and a view of all child tasks where they are assigned to project in the Parent row. I would like to do this for late child tasks, child tasks without due date and an overall "All tasks" rollup and count (reference the parent row project for clarification of what the task is). Please help! Thank you!

Answers

  • Don Wilcox
    Options

    Have you tried creating a helper column that lists the parent (using the

    =if(count(children([project task])>0,ancestors[project task])?

    Then another helper column (#2) that displays a 1 if the child is late. Then create a 3rd helper that joins helper 1 and the project task (child task). Now, in your report, in the filters, if helper2 = 1, then show helper 1. In your "All Tasks" report, instead of [Project Task] you would use [Helper 3].

    Hope this helps.

  • kellysjostrom
    Options

    So I have a formula to note a parent child : =IF(COUNT(CHILDREN(Tasks@row)) > 0, 1)

    I also have a health column with anything that is due within 7 days or overdue, we could use this as a second reference : =IF(ISBLANK([Task Due Date]@row), "", IF([Task Due Date]@row - TODAY() > 7, "Green", IF([Task Due Date]@row < TODAY(), "Red", "Yellow")))

    What formula would I use to join the two?

    Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!