Checking to see if Children are blank

Frank S.
Frank S. ✭✭✭✭✭✭

I'm trying to create a formula to check and see if children tasks are blank. If they are blank the formula cell should be blank if not blank will complete another if statement.

Here is the if statement I want to happen if the children are not blank:

=IF(ABS(SUM(CHILDREN([JUL 21]3:[JUL 21]42))) = 0, [JUN 21]44, [JUN 21]44 - [JUL 21]45)

Here is my attempt at the formula:

=IF(ISBLANK(CHILDREN([JUL 21]3:[JUL 21]42)), "Blank", "Not")

I appreciate any suggestions or where I'm missing something.

Frank Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

If my response helps, please mark it as an accepted answer. 😎

Best Answer

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓

    =if(isblank(join(children())),"Blank","Not"


    This will report if the current column's children are blank. If you want to reference a different column, or from a different row, then put the column to be analyzed and the row of the parent cell in the children part of the formula. Children(*HERE*))). example below


    =if(isblank(join(children([jul 21]2))),"Blank","Not"

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓

    =if(isblank(join(children())),"Blank","Not"


    This will report if the current column's children are blank. If you want to reference a different column, or from a different row, then put the column to be analyzed and the row of the parent cell in the children part of the formula. Children(*HERE*))). example below


    =if(isblank(join(children([jul 21]2))),"Blank","Not"

  • Frank S.
    Frank S. ✭✭✭✭✭✭

    L@123

    Forgot totally about JOIN, adding this works perfectly.

    Thanks!

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

    If my response helps, please mark it as an accepted answer. 😎

  • @L_123 This is a great answer for if you want to know in bulk if all children are blank or if any children are not blank.


    Taking it a step further, how could I check if each child is blank individually?

    For example, I have a parent that I want to display the notes of it's children. I currently have a formula that does this, with the condition that an item is not complete (i.e. [% Complete] < 100%):

    =JOIN(COLLECT(CHILDREN(Notes@row), CHILDREN([% Complete]@row), "<1"), "; ")

    But if all 8 children are blank, I get this result:

    ; ; ; ; ; ; ;

    I'd like to only collect the children that have notes, and all the permutations I've tried with if statements and isblank inside the collect function don't seem to work. Any help for this situation?

  • L_123
    L_123 ✭✭✭✭✭✭

    =JOIN(COLLECT(CHILDREN(), CHILDREN(), NOT(ISBLANK(@cell))), "; "

    You could also do something like

    =JOIN(COLLECT(CHILDREN(), CHILDREN(), NOT(ISBLANK(@cell))), char(10

    which imo is easier to read

  • Jim B
    Jim B ✭✭✭

    Hello - I'm struggling a bit with something similar here. I need to concatenate my task list for a separate report but my guys like to leave blank rows here and there on our sheets so once I've done my JOIN formula in Column 3....

    =JOIN(CHILDREN([Column2]@row), ", ")

    .....I'm left with all these stray commas which looks a bit unprofesh on the outgoing report.

    I've been trying to adapt @L_123 's formula above to ignore the blank children and only concatenate not blank children. But the formula in Column 4 is failing:

    =JOIN(COLLECT(CHILDREN([Column2]@row), CHILDREN([Column2]@row), NOT(ISBLANK(Children(Column2@row))), ", ")

    What am I doing wrong?

    Thanks in advance.

  • L_123
    L_123 ✭✭✭✭✭✭

    looks like a parenthesis error

    =JOIN(COLLECT(CHILDREN([Column2]@row), CHILDREN([Column2]@row), NOT(ISBLANK(Children(Column2@row)))), ", "

  • Jim B
    Jim B ✭✭✭

    Thanks. But it's still UNPARSABLE. Sounds like I am in the right ballpark though so I will have a play around with the ))) and see what I can come up with.

    I appreciate your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!