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

Ben Donahue · 08/16/19

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.

Using

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

or

=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 · 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: https://help.smartsheet.com/articles/2476176-formula-error-messages#circularreference

Ben Donahue · 08/19/19

Alejandra,

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 smartsheet.com to help me with that. What a powerful tool/function that is for me now, and moving forward.

Thanks again!

Ben

Paul Newcome · 08/19/19

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.

So

[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?

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

Comments

Help Article Resources

Categories