Updating a Parent Row Based on Children's Status

Hello!

We are trying to create a formula to accommodate the following logic: IF any [RFP Status] of children HAS In Progress or Canceled return the value "In Progress", IF ALL [RFP Status] of children HAS Complete or Canceled return the value "Complete", Otherwise, Not Started.

I generated the formula below using AI, however, it's returning a value of Complete when some tasks are still In Progress. Please help :)

=IF(OR(CONTAINS("In Progress", CHILDREN([RFP Status]@row)), CONTAINS("Canceled", CHILDREN([RFP Status]@row))), "In Progress", IF(AND(COUNTIF(CHILDREN([RFP Status]@row), "Complete") + COUNTIF(CHILDREN([RFP Status]@row), "Canceled") = COUNT(CHILDREN([RFP Status]@row)), COUNTIF(CHILDREN([RFP Status]@row), "Complete") > 0), "Complete", ""))

Find more posts tagged with

Formulas

Accepted answers

DKazatsky2

Hi @Tony Fronza,

See if this works out better for you.

=IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "Canceled") = COUNT(CHILDREN()), "Complete", "Not Started"))

Notes:

This can only be put into the parent row, cannot be a column formula
If any of the [RFP Status]@row cells are blank, this will not work as the COUNT formula only includes cells with values

Hope this helps,

Dave

All comments

DKazatsky2

Hi @Tony Fronza,

See if this works out better for you.

=IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "Canceled") = COUNT(CHILDREN()), "Complete", "Not Started"))

Notes:

This can only be put into the parent row, cannot be a column formula
If any of the [RFP Status]@row cells are blank, this will not work as the COUNT formula only includes cells with values

Hope this helps,

Dave

Tony Fronza

@DKazatsky2 Thank you! This works for everything except for Not Started. The only time Not Started should appear is if ALL child rows are Not Started; otherwise, it should be in Progress. Any thoughts?

Tony Fronza

@DKazatsky2 never mind! There was an issue with the sheet's formatting. It's working now! Thanks again.

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.