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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@toni.toni601 You would use a DISTINCT function wrapped around the collect.
=JOIN(DISTINCT(COLLECT(................................)), CHAR(10))
Answers
-
You would use this in another multi-select column:
=IF([Column X]@row = "TRUE", [Column Y]@row)
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
Thanks, @Paul Newcome! 🙂 Good catch.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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))
-
You all are the GREATEST
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!