Automation of Status Drop Down at the Parent Level

Options

Hi,

I want to automate the status column of the parent row based on the child rows.

For example, if all of the child rows are left blank or marked "Not Started," I want the parent row to read, "Not Started"

Or if a majority of the child rows are marked "In Progress" and some are marked "Complete" I'd like the parent row to read, "In Progress."

And finally, if all the child row tasks are marked complete the parent row automatically marks complete.


I can not figure out how this is done. Hopefully, this is possible and someone can help.

Best Answer

Answers

  • Krista Cornew
    Options

    Wow thank you! This was super helpful. I would have never been able to write this myself!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    I'm so glad it worked for you! Let me know if you need to add any more criteria and I'm happy to help. 🙂

  • Chris Jernigan
    Options

    This is super helpful. Is there anyway to add another State of Impeded?

    If any child row is Impeded then the parent row should show Impeded.

    I try to modify the formula but it only laughs at me 😁

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Chris Jernigan

    Yes, no problem! You would want to add that statement at the very beginning, the first rule for the formula to look at:

    =IF(CONTAINS("Impeded", CHILDREN()), "Impeded"


    Add that to the start, and add an extra closing parenthesis at the very end of the entire formula to close it off. Try this:


    =IF(CONTAINS("Impeded", CHILDREN()), "Impeded", IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(AND(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "") = 0), "Complete", IF(OR(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "Not Started") + COUNTIF(CHILDREN(), "") = COUNT(CHILDREN())), "Not Started", "In Progress"))))


    If that doesn't work, let me know all instances of when you want the parent row to show "Impeded" and we can add in other criteria.

    Cheers!

    Genevieve

  • Chris Jernigan
    Options

    That is perfect!

    Thank you for the quick response.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options
  • Jorden Thomas
    Options

    Could I use the formulas above (with alterations) to change a dropdown status of a child row based on the parent? Example: Parent Row = Priority Contracts for Review; when I move a Child row under that Parent row, I would like the Child's status to be updated to match the Parent status (Priority Contracts for Review).


    The status updates in the card view, but I would like the status to automatically update when I move the row in grid view.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Jorden Thomas

    If you're using that dropdown column for your lanes in card view, adding a formula to the column means that you won't be able to manually move cards (or if you do, it will erase the formula).

    Could you perhaps post two screen captures with your current set-up in grid view and card view?

    A simple option would be to add a helper column in your sheet and use the following formula:

    =PARENT(Status@row)

    This will bring in the Parent data from a column called "Status" to this child row, but in a different column.

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!