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
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.8K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!