Using If Formula to Populate Status of Parent Row

Bridgett Alonso

I am looking for help writing a formula to populate a parent row of a Single Select Dropdown column.

The Parent row should reflect the Status of the Child rows - for instance, if any of the Child row statuses are set to Not Started, In Progress, Follow Up Required or On Hold, I want the Parent status to be In Progress. If all of the Child rows statuses are set to Complete, I want the Parent row status to be Complete. If all of the Child row status are set to Skip, the Parent row Status should be Skip. I've been trying different combinations but have yet to get a version to work and continue to get #UNPARSEABLE.

Any help that could be provided would be very much appreciated!!!!

David Joyeuse

Hi @Bridgett Alonso

Really sorry that I missed your tag from last week.

To sum you up Parent row should display:

• "Complete" when all children are "Complete".
• "Not started" when all children are "Not Started".
• "Skip - Not Applicable to District" when all children are "Skip - Not Applicable to District"
• "District Follow Up Required" when all children are "District Follow Up Required"
• "In Progress" for everything else.

So here we go:

=IF(COUNTIFS(CHILDREN(), "District Follow Up Required") = COUNT(CHILDREN()), "District Follow Up Required", IF(COUNTIFS(CHILDREN(), "Skip - Not Applicable to District") = COUNT(CHILDREN()), "Skip - Not Applicable to District", IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "In Progress"))))

Let me now how this work for you.

Hope it helped!

Bridgett Alonso

Hey @David Joyeuse!

Thank you so much! That really did the trick! You have no idea how much this is going to help me and our team with our work!

Thank you very very much!

David Joyeuse

Hi @Bridgett Alonso

Could you please list all of your different statuses possible in the Status column? That could help making a faster nested IF formula rather than listing all parameters :)

Bridgett Alonso

Hi David,

Here are the statuses in the Status column:

Not Started

Skip - Not Applicable to District

In Progress

District Follow Up Required

Complete

On Hold - Dark Days Task

Here is the formula that is kind of working right now:

=IF(CONTAINS("Not Started", CHILDREN()), "Not Started", IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(CONTAINS("District Follow Up Required", CHILDREN()), "District Follow Up Required", IF(CONTAINS("Skip - Not Applicable to District", CHILDREN()), "Skip - Not Applicable to District", IF(CONTAINS("On Hold - Dark Days Task", CHILDREN()),"On Hold - Dark Days Task", IF(CONTAINS("Complete", CHILDREN()),"Complete", "In Progess")))

What I'm struggling with is this - when all of the Child rows are set to the same status (if there is more than one child row), I want the Parent row to reflect that status. Right now, if all of the Child rows are set to "Not Started", the Parent Row shows "In Progress". The only time the Parent row should be anything other than "Complete" or "In Progress" is if all of the Child rows have the same value such as "Not Started" or "District Follow Up Required".

Any help you can lend would be so very much appreciated!!!!

Bridgett Alonso

Hi @David Joyeuse,

I forgot to tag you in my response but I've replied with the statuses and with the formula that is kind of working but not exactly like I would like it to work.

Again, thank you for any help you can provide. :)

Bridgett

Bridgett Alonso

Is someone able to help me with this formula? I really need to get this figured out.

David Joyeuse

Hi @Bridgett Alonso

Really sorry that I missed your tag from last week.

To sum you up Parent row should display:

• "Complete" when all children are "Complete".
• "Not started" when all children are "Not Started".
• "Skip - Not Applicable to District" when all children are "Skip - Not Applicable to District"
• "District Follow Up Required" when all children are "District Follow Up Required"
• "In Progress" for everything else.

So here we go:

=IF(COUNTIFS(CHILDREN(), "District Follow Up Required") = COUNT(CHILDREN()), "District Follow Up Required", IF(COUNTIFS(CHILDREN(), "Skip - Not Applicable to District") = COUNT(CHILDREN()), "Skip - Not Applicable to District", IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "In Progress"))))

Let me now how this work for you.

Hope it helped!

Bridgett Alonso

Hey @David Joyeuse!

Thank you so much! That really did the trick! You have no idea how much this is going to help me and our team with our work!

Thank you very very much!