Parent row based on children status and due date formula

Hello, I have a sheet that tracks event deliverables for 150 or so events each year. The way the sheet is set up each parent row is the event name, and then the children rows are each task for that event. I have a column for Status with the options for Complete, Not Started, and In Progress. I want to create a new column called Event Status that uses the RYG Balls to be able to collapse the children and see a broader view of the event status as a whole.

To do this, I'm trying to create a formula next to each parent row. I specifically want it to say that IF the due date is in less than 5 days and all children rows are marked complete, then green shows. Same goes for IF the due date is in less than 5 days and all of the children rows are NOT marked complete, then red shows. The third option is IF the due date is greater than 5 days and some children rows are marked complete then yellow shows.

Is all of that possible?

Find more posts tagged with

Non-profit

Formulas

Marketing

Comments

Mike Andreas

I recommend that you examine a question I have recently answered. Let me know if this doesn't meet your need and I will be happy to help further!

marym

We do a similar function on a RYG column based on a column called % Complete. If it's 100 percent it's blue. If the row has children and any are red, the parent becomes red (same for yellow and green). If you change this to use your days < 5 it may work for you.

=IF([% Complete]4 = 1, "Blue", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(OR(COUNTIF(CHILDREN(), "Green") > 0, [% Complete]4 > 0), "Green", ""))))

emily.pfeifer74276

I've been trying to find a combination of this effect and past-due status and I have had no luck in modifying or combining formulas. I've probably put a week's worth of effort in this, and I can get partway there, but not completely. I wish something like this was offered in the formulas template because I know people have requested something similar or had Smartsheet staff supply them with a similar solution during set-up and training (saw this in my last job!).

I'd like to have both a parent-row-level indicator that something is incomplete. For us to judge it incomplete, it should both have an End Date in the past and be less than 100% Complete. It's easy to do this in conditional formatting but really frustrating to do it in formulas.

So a child row should get flagged if it's incomplete and the end date is in the past. The parent row should get flagged if ANY of the children row are flagged, so I don't have to expand a ton of content and scroll through a long sheet.

I'd just like an indicator that something in the children rows is amiss and needs to be addressed, but I'd like it to be a visual indicator (rather than triggering alerts).

In my screenshot I have a test sheet with parent row at the top. You can see I've got the RGB formula working (first white row at the bottom). I'd like to add the second formula in somehow (second white row, highlighted).

Can someone help? Thanks in advance!