Joining the Results of Boxes Checked on a Form Entry (2 Solutions)
Edit: I had used numbers as my column names which in turn populated my first row. Another user made a good point that the number 1 in the first row will not actually duplicate my column name, but will read as "true" since it is a checkbox column.
The column names and formulas have been updated to account for this.
.
I think it's best explained with an example... We have 5 checkboxes that we will just call A through E for simplicity. User 1 selects A, C and E. User 2 selects D. User 3 selects A and B.
The sheet layout (column headers in bold):
User A B C D E
A B C D E
User 1 y y y
User 2 y
User 3 y y
.
Here is how the basic JOIN(COLLECT would look using a ", " as the delimiter.
=JOIN(COLLECT(A$1:E$1, A@row:E@row, 1), ", ")
User 1: A, C, E
User 2: D
User 3: A, B
Nice and clean with no extra commas cluttering things up. Usually good enough. But then a coworker laid down a challenge. Replace the last comma (if any) with the word "and".
.
Well this pretty little mess accomplished that:
=SUBSTITUTE(JOIN(COLLECT(A$1:E$1, A@row:E@row, 1), ", "), ", ", " and ", COUNTIFS(A@row:E@row), 1) - 1)
User 1: A, C and E
User 2: D
User 3: A and B
.
Hope this helps anyone who happens to stumble upon this little thread...
Comments
-
Paul,
Excellent work!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks Andree! Never really had need for the second option. I just had someone ask if it was possible. Hahaha.
-
Haha! You're welcome!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I like the potential of this workaround but I'm having issues with the formula. See screen shots. It's not displaying the column header that was checked. Just true,true,true. No answer from the column headers. And then oddly the second one is saying true,false. Any ideas what's not working?
Thank you for your help!
-
Hi,
It seems like you missed adding the top row that should be the same as the column names.
Did it work when you added that row?
Hope that helps!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thank you. That worked. However, there seems to be a glitch with the number 1 begin accepted in the checkbox field. 2-5 worked fine.
I ended up just using the content that I wanted in my actual sheet and it worked fine! Thank you so much!
-
Excellent!
Happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I am actually glad you brought this up. I never use just plain numbers when I have this set up for a practical application, so I hadn't even considered the 1 giving a "true" value when repeating the column headers in the first row.
Thanks for bringing this to my attention. I am going to go ahead an edit my post for clarity based on this.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!