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
-
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.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@toni.toni601 You would use a DISTINCT function wrapped around the collect.
=JOIN(DISTINCT(COLLECT(................................)), CHAR(10))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
You would use this in another multi-select column:
=IF([Column X]@row = "TRUE", [Column Y]@row)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
In column X, I have size XS. In column Y I have customers: Paul, Jack, Jane.
I want the automation to check my size column for all the XS and only list Paul, Jack, Jane in the cell.
The formula you gave me, didn't work... =IF([{your size}]@row = "XS", [{Business customers}]@row)
This formula resulted in #unparseable
any other ideas?
-
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.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Also note that you should use either curly brackets for a cross sheet reference or square brackets for referencing a column on the same sheet as the formula. You should not use both which is why your IF statement is throwing that particular error.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks, @Paul Newcome! 🙂 Good catch.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
That worked but how do I remove the duplicates?
-
@toni.toni601 You would use a DISTINCT function wrapped around the collect.
=JOIN(DISTINCT(COLLECT(................................)), CHAR(10))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
You all are the GREATEST
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!