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

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))

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

Find more posts tagged with

Formulas

Comments

Alejandra

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

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

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Netdays Formula
I am wanting to calculate Netdays but only if there is data in 'Corrective Action 1' I am currently using this formula: =IF(ISBLANK([Date Action 1 Complete]@row ), NETDAYS([Date of Event]@row , TODAY()), NETDAYS([Date of Event]@row , [Date Action 1 Complete]@row )) How do I adjust it to only pull a date when Corrective…
Re-write a formula to make it Column Formula compatible
I have Concept phase projects that can result in multiple Phase 1 projects being generated from one Concept Review. I want to calculate the time in the Concept Phase for the first project from that Concept (the Approved Gate 0 UID), but mark it N/A for any subsequent references to that UID. I figured out the formula below…
How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.