Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
Sign in to join the conversation:
The posts in this section are historical and no longer monitored for accuracy. If this discussion interests you and you'd like to join in, please visit the active Community to post and engage.
Dears:
I'm talking about one Date formated column. When my Parent cell has that formula and I have 4 children's (2 filled and 2 unfilled) the formula returns the MAX value between the 2 filled cells. I want for my Parent the MAX from all four childrens. I need to return the MAX value only when the 4 childrens are filled. What can I do in order to avoid any result while there are any children without information?
Luis,
Will you always know how many children there are (filled and unfilled)?
Assuming the answer is "yes - always 4" then
=IF(COUNT(CHILDREN()) = 4, MAX(CHILDREN()), "Unfilled remain")
The formula for "no" is different and longer, but also possible.
Hope this helps.
Craig
Try this:
=IF(COUNTIF(CHILDREN(), "") + COUNT(CHILDREN()) = COUNT(CHILDREN()), MAX(CHILDREN()))
This will count the number of children that contain a date (COUNT(CHILDREN())) and add it to the number of children that are blank (COUNTIF(CHILDREN(), ""). If this number is the same as the count of children that contain a date (meaning they all contain a date) then it will show the max. This will work regardless of the number of children.
Note - this will look for any value in the children, not just dates.
Enjoy!
Thank you both. I'll keep the Travis solution because it's more generic.
Many thanks.
Travis, Luis, J Craig:
Thank you for the discussion, it enabled me to resolve my roadbloack as well.
Best.
Jon
