CHILDREN of two columns

Options

I was trying to reference the children of two columns @row in a MIN(COLLECT()) function.

Currently, I am only able to use two separate COLLECT() ---

MIN(COLLECT(CHILDREN(col1@row, criteriaRange1, criteria1), COLLECT(CHILDREN(col2@row, criteriaRange1, criteria1))

But when I arrange the two columns together, MIN(COLLECT(CHILDREN(col1@row:col2@row), criteriaRange1, criteria1)), the result is #UNPARSEABLE.

Is there any way to make the formula shorter?

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hey @CeliaC

    Yes, you're right. You would need to use MIN(COLLECT around each Child rows with the criteria, then get the MIN of that as well:

    =MIN(MIN(COLLECT(CHILDREN(col1@row), criteriaRange1, criteria1)), MIN(COLLECT(CHILDREN(col2@row), criteriaRange1, criteria1)))

    Let me know if that makes sense and works for you!

    Cheers,

    Genevieve

  • CeliaC
    CeliaC ✭✭
    Options

    I realized that CHILDREN() only applies to a single cell - I'll have to use parallel formulas to refer to the childre cells in multiple columns.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!