Cell to hold multiple values
Hello,
I am trying to create a formula (or if there is a better way please let me know) for a cell to have multiple values if true. So i have one column with a drop down with 10 different options and 10 more columns. If someone picks the first option then they only need to fill out 2/10 columns and if they pick the second option they only need to fill out 3/10. Some of the columns they have to fill might overlap. I basically want a formula/way where I can have one formula cell and it will have the information that they fill out after choosing 1 of the 10 options otherwise there are 20 different columns they have to look at and is not practical. To the same, some of the option might only have a column specifically for that option and will not overlap. Below is a very simple example.
So in a sense I would want a 7th row that would say "1, 2, 3" or "3, 6" or "2, 2, 4, 5" so it is listed out.
Answers
-
Hi @Jeremy Oesch,
You can do this with a JOIN & COLLECT formula.
As an example, with 6 columns (1-6):
Where the formula in Combined is:
=JOIN(COLLECT([1]@row:[6]@row, [1]@row:[6]@row, @cell <> ""), ", ")
You can amend this with the relevant column names (it will work just as well with more/less columns involved).
Hope this helps, but if I've misunderstood something or you've any problems/questions then just let us know!
-
Hi @Nick Korna,
Thank you! I am a little confused on the last part of the formula. Can you explain it in more detail on what the @cell is and the other infront of it?
@cell <> ""), ", ")
-
I hope you're well and safe!
@cell means that each cell in the range is checked
<>" means Isn't blank
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
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.
-
It works great!
Thank you!
-
Hi @Jeremy Oesch,
@Andrée Starå has already outlined what these bits do in technical terms. In this case, what they're doing is making it so the output gives the right number of comma separators. If just a JOIN was used, without the COLLECT section you'd end up with results like this:
Obviously this would be less than ideal! 😉
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!