Find blank children and return another column for that row as 'join distinct'

Hi - hoping someone can help.

I have a parent row where I want to return: 'Complete' if all children have a value in that column, if any are blank, I want to return another column for that row.

If this is too complex I will settle for 'Complete' if all children have a value or 'Incomplete' if at least one child is blank.

See below for expected outcome:

this is the formula I was hoping would work for the full outcome but it's erroring:

=JOIN(COLLECT(CHILDREN([Fulfilment Country]@row), CHILDREN([Fulfilment Country]@row), ISBLANK(CHILDREN())), CHAR(10))

Many thanks for any help in advance.

Answers

  • melimob
    melimob ✭✭✭

    OK - I have figured out a formula that works for my needs so have posted below. Would still be interested though if anyone has an answer for returning and collating from another row.

    result: returns 'Pending' if at least one child is blank, or 'complete' if all have a text value.

    =IF(COUNTIF(CHILDREN(), ISBLANK(@cell)) > 0, "Pending", "Complete")

    or

    =IF(COUNTIF(CHILDREN(), ISBLANK(@cell)) > 0, COUNTIF(CHILDREN(), ISBLANK(@cell)), "Complete")

    result: returns the number of blank children if at least one child is blank, or 'complete' if all have a text value.

    hope this helps.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @melimob

    Well-done! The first formula looks good 🙂

    To return the country names where there's a blank child cell, try this:

    =IF(COUNTIF(CHILDREN(), ISBLANK(@cell)) > 0, JOIN(COLLECT(CHILDREN([Fulfilment Country]@row), CHILDREN(), ""), CHAR(10)), "Complete")


    Let me know if this works for you.

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!