JOIN COLLECT for Checkboxes

Options

Hello All,

Is there a way to collect information using a JOIN COLLECT formula?

Column 2 is Name

Column 3 thru 5 ASL.

Columns 6 thru 8 is are the names I want to populate based on the checkboxes (ASL).

Column 3 can have two checkboxes versus 1. Ultimately I would like all checkboxes that are checked to populate names in Column 6.

Column 3 with checkboxes marked to populate names in Column 7

Column 4 with checkboxes marked to populate names in Column 8.

I am using =JOIN(COLLECT{[COLUMN2:COLUMN2], [COLUMN3:COLUMN3], 1 "Support")], CHAR(10))

Am I totally missing something? Thank you in advance for your help!

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @hello1030

    I hope you're well and safe!

    Try something like this. (I've guessed what your formula would look like, but you'd have to update the column names to match yours)

    =JOIN(COLLECT([COLUMN2]:[COLUMN2], [COLUMN2]:[COLUMN2], 1, [COLUMN3]:[COLUMN3], "Support"), CHAR(10))

    Did that work/help?

    There were a lot of errors in the formula, but it was mainly the brackets { is used for cross-sheet formulas, and you have to close the [ ] square brackets around a column name that doesn't only have one piece of text, and then the structure wasn't correct.

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭✭
    Options

    Hi There,

    Some sample data might help to understand the situation better. However, from first glance, it looks like you are not stating the delimiter to be proper. Usually, the delimiter would be a hyphen, or a comma, or a slash. In your formula, it is not clear what the delimiter is to join the values.

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @hello1030

    I hope you're well and safe!

    Try something like this. (I've guessed what your formula would look like, but you'd have to update the column names to match yours)

    =JOIN(COLLECT([COLUMN2]:[COLUMN2], [COLUMN2]:[COLUMN2], 1, [COLUMN3]:[COLUMN3], "Support"), CHAR(10))

    Did that work/help?

    There were a lot of errors in the formula, but it was mainly the brackets { is used for cross-sheet formulas, and you have to close the [ ] square brackets around a column name that doesn't only have one piece of text, and then the structure wasn't correct.

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • hello1030
    Options

    Thank you both!

    @AravindGP Please see photo.

    @Andrée Starå I tried this in the formula and used "Accountable" versus support and those were the results.

    The outcome is I would like under the Accountable Column to list Ashlee Brown, Lina Baker, and Kesha Porter. Then under Support Column I would like to list Ashlee Brown and Bobby Gough. When Henry Flay is selected, it should populate under one of the columns once the checkbox is selected.

    Thank you so much again!

  • hello1030
    Options

    I was able to make it work using both comments. Thank you so much for your help again!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @hello1030

    Excellent!

    Happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!