Trying to copy a multi select cell data into another cell once criteria is met


If X is true, copy cells in Y

What formula should I use for this?

Keep in mind, cell Y is a multi select cell.

Best Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @toni.toni601

    Thanks for providing more information! It sounds like you want to evaluate an entire column, not individual rows, and that you're building this formula in a second sheet (not the current sheet with the columns).

    In this instance, you could use a JOIN formula to join together all of your customers, and then use COLLECT to filter down by size. See: Formula combinations for cross sheet references

    For example:

    =JOIN(COLLECT({Business customers}, {your size}, "XS"), ", ")

    Notice at the end I have a comma to separate your values in the JOIN function. However since you noted your column is multi-select, you'll want to build the formula in a multi-select column as well and use CHAR(10) as the separating character, like so:

    =JOIN(COLLECT({Business customers}, {your size}, "XS"), CHAR(10))

    If this didn't help, it would be useful to see screen captures of your source sheet, but please block out sensitive data.



    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @toni.toni601 You would use a DISTINCT function wrapped around the collect.

    =JOIN(DISTINCT(COLLECT(................................)), CHAR(10))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!