Cell to hold multiple values

Jeremy Oesch

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.

Nick Korna

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!

Jeremy Oesch

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 <> ""), ", ")

Andrée Starå

Hi @Jeremy Oesch

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!

Jeremy Oesch

It works great!

Thank you!

Nick Korna

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! 😉