Checking to see if Children are blank

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.

Find more posts tagged with

Accepted answers

L_123

=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"

All comments

L_123

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

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

Frank S.

L@123

Forgot totally about JOIN, adding this works perfectly.

Thanks!

Ben Swarner

@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

=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

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

looks like a parenthesis error

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.