JOIN/COLLECT multiple checkboxes into a text/number column
Hi all,
Using Paul Newcome' solution in this post, I came up with the following working formula: =JOIN(COLLECT($[Add to ABE waitlist]$1:$[Add to Transitions waitlist]$1, [Add to ABE waitlist]@row:[Add to Transitions waitlist]@row, @cell = 1), " AND ")
But I can't convert it to a column formula because of the absolute references. So I found this community post and created the Waitlist helper sheet summary field with the following formula: =JOIN([Add to ABE waitlist]1:[Add to Transitions waitlist]1, " AND ")
When I update the Waitlist formula to =JOIN(COLLECT([Waitlist helper]#, [Add to ABE waitlist]@row:[Add to Transitions waitlist]@row, @cell = 1), " AND ") I get an incorrect argument set error.
Any insight would be GREATLY appreciated.
Thanks,
Joanna Cardinal (she, her, hers)
Best Answer
-
2 ways this can be done:
Option #1:
Use the INDEX function to feed the first row into your formula:
=JOIN(COLLECT(INDEX([Add to ABE waitlist]:[Add to Transitions waitlist], 1), [Add to ABE waitlist]@row:[Add to Transitions waitlist]@row, @cell = 1), " AND ")
Option #2:
You can have this in a helper sheet and bring it in as a cross sheet reference...
This could make issues if someone moves columns around, but you can also have this helper sheet actually be pulling the data from this main sheet (so that it is always in sync)
Let me know if i am making sense...
Answers
-
2 ways this can be done:
Option #1:
Use the INDEX function to feed the first row into your formula:
=JOIN(COLLECT(INDEX([Add to ABE waitlist]:[Add to Transitions waitlist], 1), [Add to ABE waitlist]@row:[Add to Transitions waitlist]@row, @cell = 1), " AND ")
Option #2:
You can have this in a helper sheet and bring it in as a cross sheet reference...
This could make issues if someone moves columns around, but you can also have this helper sheet actually be pulling the data from this main sheet (so that it is always in sync)
Let me know if i am making sense...
-
Index, of course. Thank you very much @Leibel S.
Joanna Cardinal (she, her, hers)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!