COLLECT in CHILDREN based on another column's value in CHILDREN scope

Ben Donahue
Ben Donahue ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

I am trying to find the min value of CHILDREN in a column of numbers where the value of the [% Complete] cell in the row of that column is not = 100%, but, again, only for the CHILDREN. 


=MIN(COLLECT(CHILDREN(), [% Complete]:[% Complete], @cell < 1))


=MIN(COLLECT(CHILDREN([Days Till Due]:[Days Till Due]), CHILDREN([% Complete]:[% Complete]), <1))

in my [Days till due] column, for example, does not work. I get a #CIRCULAR REFERENCE error

On a related note, what kind of argument does CHILDREN take? 

Thank you.



  • Alejandra
    Alejandra Employee
    edited 08/19/19

    Hi Ben,

    The CHILDREN function will allow you to reference a cell in a parent row. If no cell is specified, the function references the children of the current cell.

    The second formula should look something like this:

    =MIN(COLLECT(CHILDREN([Days Till Due]1), CHILDREN([% Complete]1), <1))

    If needed, more information on formula errors can be found here:

  • Ben Donahue
    Ben Donahue ✭✭✭✭✭


    Firstly, Thank you!! I really appreciate your help in this matter. I was getting frustrated beyond reason. Understanding what a circular reference is, which seemed self-explanatory, gave no insight into how to fix it. Your explaining what the argument for CHILDREN is and how it works, now that was a god-send. I could find nothing in at to help me with that. What a powerful tool/function that is for me now, and moving forward.

    Thanks again!


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    A little more detail to Alejandra's explanation...


    When using the CHILDREN function, you will want to reference a specific cell (or none if you want to reference the cell the formula is residing in), but referencing a RANGE within the CHILDREN function will not work.




    [Column Name]1

    will work, but

    [Column Name]:[Column Name] 

    will not.


    In addition to the above (and correct me if I am wrong but) isn't specifying less than a number in the MIN function kind of redundant?


    It's like saying to pull the lowest number from the low range. If you are already looking for the lowest number, I feel like it would already be looking at the low range.


    You could save yourself a little bit of work by just saying


    =MIN(CHILDREN([Column Name]1))


    Do you have a use case where specifying the low range is needed?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!