Updating Status Based on RYGG symbols of Children Rows and RYGG Formula

New day, new formula conundrums!

For these formulas, I'm looking to update the status of my PARENT rows (2 levels) based on the RYGG symbol as well as having the RYGG symbol is based the CHILDREN rows' Task Status column. I have both working but I feel like they could be cleaner and just wanted to get a second set of eyes on it.

Here's a screenshot of my tasks:

Here's the RYGG formula as of now: =IF(Lvl@row = "0", "", IF([Task Status]@row = "Completed", "Gray", IF([Task Status]@row = "Not Applicable", "Gray", IF([Task Status]@row = "Escalation", "Red", IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY(3), "Yellow", IF([Due Date]@row >= TODAY(4), "Green")))))))

~ For statuses all RYGG should be based on the "Due Date" column. I have the following options (this will show what color I'm expecting):

Not Started (Red if past due, Gray if no date)

In Progress (Red if past due, Yellow if within 3 days, Green if not past due)

Delayed (Red if past due, Yellow all other times)

Escalation (Red)

On Hold (Red if past due, Gray all other times) <-- I don't think this is fully reflected

Completed (Gray)

Not Applicable (Gray)

Here's the TASK STATUS formula as of now: =IF(CONTAINS("Red", CHILDREN(RYGG@row)), "Escalation", IF(CONTAINS("Yellow", CHILDREN(RYGG@row)), "In Progress", IF(CONTAINS("Green", CHILDREN(RYGG@row)), "In Progress", "Completed")))

~ For the Level 1 and Level 2 statuses (Med and Light blue lines) I'd like these status to be updated based on what we see in the CHILDREN rows. I was originally going off of the symbols but I don't think that will capture everything.

Not Started (Everything is not started)

In Progress (Anything is In Progress)

Delayed (Anything is Delayed - should override all other statuses)

Escalation (Anything is "Escalation" - should override all other statuses)

On Hold (Anything is "On Hold")

Completed (Everything is "Completed")

Not Applicable (Everything is "Not Applicable")

Oof!! Thank you to anyone who can help me knock these out!

~Jaime

Find more posts tagged with

Sheets

Formulas

Accepted answers

Paul Newcome

It would actually go in the one with the "Complete".

=IF(COUNTIFS(CHILDREN(), OR(@cell = "Completed", @cell = "Not Applicable")) = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Completed"

Basically we want to count all child cells that are either "Complete" or "Not Applicable" and if the count of those cells is the same as the count of all child cells, then output "Complete".

All comments

Jaime Elizabeth

After looking at this over the weekend, I realized that basing my PARENT Status off of the RYGG is not going to work. Mostly because I have multiple use cases for the Red, Yellow, and Gray status balls. So, instead, I would like to build the PARENT Status formula off of the CHILDREN in the "Task Status" column. Here's what I'm still looking to do there:

Not Started (Everything is not started)

In Progress (Anything is In Progress)

Delayed (Anything is Delayed - should override all other statuses)

Escalation (Anything is "Escalation" - should override all other statuses)

On Hold (Anything is "On Hold")

Completed (Everything is "Completed")

Not Applicable (Everything is "Not Applicable")

I believe I was also able to update my RYGG formula when I came back with fresh eyes so no further assistance needed there.

I'm going to keep plugging away, but thank you in advance if someone is able to assist!

~Jaime

Jaime Elizabeth

Not Started (Everything is not started)

In Progress (Anything is In Progress)

Delayed (Anything is Delayed - should override all other statuses)

Escalation (Anything is "Escalation" - should override all other statuses)

On Hold (Anything is "On Hold")

Completed (Everything is "Completed")

Not Applicable (Everything is "Not Applicable")

Here's an updated screenshot of what I have working so far:

Here's my current formula: =IF(COUNTIFS(CHILDREN(), @cell = "Completed") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Completed", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Not Started", IF(COUNTIFS(CHILDREN(), @cell = "Not Applicable") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Not Applicable", IF(COUNTIFS(CHILDREN(), "Escalation") > 0, "Escalation", IF(COUNTIFS(CHILDREN(), "Delayed") > 0, "Delayed", IF(COUNTIFS(CHILDREN(), "On Hold") > 0, "On Hold", "In Progress"))))))

@Paul Newcome I saw a few of your posts related to these types of formulas and I was wondering if you may be able to help 1) make sure how I have this setup is accurate (It seems so!) and 2) I'm hoping you can help me figure out ONE more thing...

In the screenshot above you'll see that I have a "Not Applicable" statuses on rows 4 and 23-26. Since everything else is "Not Started", I'd ideally like to have rows 2 and 3 show as "Not Started" instead of "In Progress". Is this possible without making my default "Task Status" for Rows 4 and 23-26 "Not Started"?

Thank you in advance for your help!!!

~Jaime

Austin Smith

Answer to the last question is yes. Sum the N/A value into the Not Started and Completed value calculations. You want the calc to assume that N/A is no different from the other answers given, as it effectively removes the row from the count. (count of not started + count of n/a = count of rows) You could do the reverse and subtract the count of n/a from total rows counted. Works either way.

Working on the formula build and veracity. But it generally looks right at first glance. Will circle back if/when I can, and if @Paul Newcomesomeone doesn't beat me.

Jaime Elizabeth

@Austin Smith Thank you for responding.

I'm assuming you mean to update the following part of the formula (for Completed and Not Started), but am not sure how to change the syntax. Can you assist with an example?

=IF(COUNTIFS(CHILDREN(), @cell = "Completed") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Completed"

Would it go here:

=IF(COUNTIFS(CHILDREN(), @cell = "Completed") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "", @cell = "Not Applicable")), "Completed"

Paul Newcome

It would actually go in the one with the "Complete".

=IF(COUNTIFS(CHILDREN(), OR(@cell = "Completed", @cell = "Not Applicable")) = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Completed"

Basically we want to count all child cells that are either "Complete" or "Not Applicable" and if the count of those cells is the same as the count of all child cells, then output "Complete".

Jaime Elizabeth

@Paul Newcome Sadly, This didn't work out exactly as I expected. Here's the updated formula I have used:

=IF(COUNTIFS(CHILDREN(), @cell = "Completed") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Completed", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Not Started", IF(COUNTIFS(CHILDREN(), @cell = "Not Applicable") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Not Applicable", IF(COUNTIFS(CHILDREN(), "Escalation") > 0, "Escalation", IF(COUNTIFS(CHILDREN(), "Delayed") > 0, "Delayed", IF(COUNTIFS(CHILDREN(), "On Hold") > 0, "On Hold", "In Progress"))))))

Here's a screenshot of what's happening now:

In Row 3, I was hoping to see "Not Started". In Row 23, I was hoping to see "Not Applicable" (because they are "all" Not Applicable. In Rows 11-17, which I hid, I have them set as "Not Started". With this combination, I was hoping Row 2 would show as to show "Not Started".

Ideal state would be:

All "Not Started" OR A combo of "Not Applicable" and "Not Started" = "Not Started"

All "Completed" OR A combo of "Not Applicable" and "Completed" = "Completed"

All "Not Applicable" = "Not Applicable"

Paul Newcome

I don't see where you included the "Not Applicable" to the various COUNTIFS using the OR as I had suggested.

Jaime Elizabeth

@Paul Newcome Apologies! I pasted the wrong formula. Here is the updated version I am using:

=IF(COUNTIFS(CHILDREN(), OR(@cell = "Completed", @cell = "Not Applicable")) = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Completed", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Started", @cell = "Not Applicable")) = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Not Started", IF(COUNTIFS(CHILDREN(), @cell = "Not Applicable") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Not Applicable", IF(COUNTIFS(CHILDREN(), "Escalation") > 0, "Escalation", IF(COUNTIFS(CHILDREN(), "Delayed") > 0, "Delayed", IF(COUNTIFS(CHILDREN(), "On Hold") > 0, "On Hold", "In Progress"))))))

Paul Newcome

I would suggest simply rearranging the order of your IFs.

You have:

Completed

Not Started

Not Applicable

Escalation

Delayed

On Hold

all else In Progress

I would suggest:

Not Applicable

Escalation

Delayed

On Hold

Completed

Not Started

all else In Progress

Jaime Elizabeth

Thank you Paul. I think that's got it.

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.