Child statuses in parent row- combined from more than 1

Hi Smartsheet Heads!

Currently I am using a formula from this thread: https://quip.com/nyDjAzhAKRhq

=IF(COUNT(CHILDREN([Localisation Status]@row)) > 0, IF(COUNTIF(CHILDREN([Localisation Status]@row), "Approved") = COUNT(CHILDREN([Localisation Status]@row)), "Approved", IF(COUNTIF(CHILDREN([Localisation Status]@row), "Not Started") = COUNT(CHILDREN([Localisation Status]@row)), "Not Started", "In Progress")))

The issue here is that I need to set a parent status to scheduled also in case when some of children statuses are "Cancelled" and cannot force this formula to work properly with e.g. OR function.

Like below, this should be also scheduled in Parent status:

Any hints please?

Thank you

Find more posts tagged with

Accepted answers

Rado

OK, I've found a typo - opening quotation was missing in formula.
It works now:

1)
=IF(COUNT(CHILDREN([Schedule Status]@row)) > 0, IF(COUNTIF(CHILDREN([Schedule Status]@row), OR(@cell = "Scheduled", @cell = "Canceled")) = COUNT(CHILDREN([Schedule Status]@row)), "Scheduled", IF(COUNTIF(CHILDREN([Schedule Status]@row), "Not Started") = COUNT(CHILDREN([Schedule Status]@row)), "Not Started", "In Progress")))

Please disregard main schedule status column, as there is an automation on it.

Thank you very much @KPH for your help :)
Have a great day ahead!

All comments

KPH

Hi @Rado

The formula you are using will change the parent row to Approved if all children are Approved or Not Started if all children are Not Started. In all other situations, it will return In Progress.

It works by counting the number of children and the number of children with specific statuses. You can include "Canceled" in the status count using an OR, by changing this part:

COUNTIF(CHILDREN([Localisation Status]@row), "Scheduled")

COUNTIF(CHILDREN([Localisation Status]@row), OR(@cell = "Scheduled", @cell = Canceled"))

or you can subtract the canceled rows from the total count. Change this:

= COUNT(CHILDREN([Localisation Status]@row))

= (COUNT(CHILDREN([Localisation Status]@row))-(COUNTIF(CHILDREN([Localisation Status]@row), "Canceled"))

The formula you shared does not return Scheduled at all, so I can't help paste these parts in for you. But reach out with your formula if you need help pasting these sections in.

Rado

Hi @KPH
Thank you for replying.

I see both are not working, I am getting formula error like this:

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas?source=apphelpicon

Btw, I made a small error for basic formula, column is named Schedule Status - not localisation status. Sorry.

Anyway, whole (column) formula for schedule status now is:

=IF(COUNT(CHILDREN([Schedule Status]@row)) > 0, IF(COUNTIF(CHILDREN([Schedule Status]@row), "Scheduled") = COUNT(CHILDREN([Schedule Status]@row)), "Scheduled", IF(COUNTIF(CHILDREN([Schedule Status]@row), "Not Started") = COUNT(CHILDREN([Schedule Status]@row)), "Not Started", "In Progress")))

It uses a helper columns to manage status and automation to set parent row status.

As mentioned, both formulas don't for for me.

1)
=IF(COUNT(CHILDREN([Schedule Status]@row)) > 0, IF(COUNTIF(CHILDREN([Schedule Status]@row), ~~OR(@cell = "Scheduled", @cell = Canceled")) = COUNT(CHILDREN([Schedule Status]@row)), "Scheduled", IF(COUNTIF(CHILDREN([Schedule Status]@row), "Not Started") = COUNT(CHILDREN([Schedule Status]@row)), "Not Started", "In Progress")))~~

^this is fixed, read post below.

2)
=IF(COUNT(CHILDREN([Schedule Status]@row)) > 0, IF(COUNTIF(CHILDREN([Schedule Status]@row), "Scheduled") = (COUNT(CHILDREN([Schedule Status]@row))-(COUNTIF(CHILDREN([Schedule Status]@row), "Canceled")), "Scheduled", IF(COUNTIF(CHILDREN([Schedule Status]@row), "Not Started") = COUNT(CHILDREN([Schedule Status]@row)), "Not Started", "In Progress")))

Did I do anything wrong?
Thank you

Rado

Please disregard main schedule status column, as there is an automation on it.

Thank you very much @KPH for your help :)
Have a great day ahead!

KPH

That's great @Rado ! Glad I could 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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.