JOIN COLLECT DISTINCT CHILDREN FROM ANOTHER COLUMN WHERE CHILDREN OF CURRENT IS TICKED

melimob
melimob ✭✭✭
edited 03/08/24 in Formulas and Functions

I have two columns.

primary and column B

On the parent row for column B, I want to look at any children which are ticked and then join collect distinct values from primary column.

If I was to do a basic formula in column B it would be this for children:

=JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), <>"")), CHAR(10))

I want to return results from Primary column only where column B is checked.

any advice?

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Try this:

    =JOIN(DISTINCT(COLLECT(CHILDREN([Primary Column]@row), CHILDREN([Primary Column]@row), <>"", CHILDREN([Column B]@row), 1)), CHAR(10))

    I made three additions, shown in bold here:

    =JOIN(DISTINCT(COLLECT(CHILDREN([Primary Column]@row), CHILDREN([Primary Column]@row), <>"", CHILDREN([Column B]@row), 1)), CHAR(10))

    This part tells the collect to use the value in the Primary Column not the column that the formula is in.

    =JOIN(DISTINCT(COLLECT(CHILDREN([Primary Column]@row), CHILDREN([Primary Column]@row), <>"", CHILDREN([Column B]@row), 1)), CHAR(10))

    This part says only collect from children that have a value in the Primary Column.

    =JOIN(DISTINCT(COLLECT(CHILDREN([Primary Column]@row), CHILDREN([Primary Column]@row), <>"", CHILDREN([Column B]@row), 1)), CHAR(10))

    This part says to only collect from children that have a checked box in Column B.

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Try this:

    =JOIN(DISTINCT(COLLECT(CHILDREN([Primary Column]@row), CHILDREN([Primary Column]@row), <>"", CHILDREN([Column B]@row), 1)), CHAR(10))

    I made three additions, shown in bold here:

    =JOIN(DISTINCT(COLLECT(CHILDREN([Primary Column]@row), CHILDREN([Primary Column]@row), <>"", CHILDREN([Column B]@row), 1)), CHAR(10))

    This part tells the collect to use the value in the Primary Column not the column that the formula is in.

    =JOIN(DISTINCT(COLLECT(CHILDREN([Primary Column]@row), CHILDREN([Primary Column]@row), <>"", CHILDREN([Column B]@row), 1)), CHAR(10))

    This part says only collect from children that have a value in the Primary Column.

    =JOIN(DISTINCT(COLLECT(CHILDREN([Primary Column]@row), CHILDREN([Primary Column]@row), <>"", CHILDREN([Column B]@row), 1)), CHAR(10))

    This part says to only collect from children that have a checked box in Column B.

  • melimob
    melimob ✭✭✭

    @KPH thank you so so much. Not only for the right answer but also taking the time to elaborate and break it down so clearly.


    Has helped me immensely.


    Thank you again!

  • KPH
    KPH ✭✭✭✭✭✭

    Thanks for the feedback. I'm pleased I could help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!