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

melimob

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.

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.

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