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.
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
-
=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
-
=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 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?
-
=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
-
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.
-
looks like a parenthesis error
=JOIN(COLLECT(CHILDREN([Column2]@row), CHILDREN([Column2]@row), NOT(ISBLANK(Children(Column2@row)))), ", "
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 405 Global Discussions
- 215 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!