IF/THEN from several checkmark columns, returning various labels in the same column

[Mary_Williams]

I read several posts on what I hoped was this if/then topic, but there are *so many if/then questions out there and I still didn’t find my answer (it all depends on my own keywords, of course). Please combine this into the right thread if it already exists.

I need to track a biannual speaker event into am/aft/eve sessions over 2 days. I have info duplicated because:

1. I’m still in the experimentation phase to see which formats work best
2. I need the info to be sortable/filterable/analyzable in many different views
3. any columns can be hidden as long as the essential result is achieved (yes messy but I’m an amateur)

I’m looking for a way that a checkmark in one of 5 columns returns a unique label in one column. I’m fine if the labels are returned in text form or multiple selection form; that’s less important. I figured out the if/then assuming an on/off for one column, but I’m lost as to how to create an if/then for on/off between 5 columns.

I included a screen shot of what I’d like it to look like with no hidden columns, but this was manually done, not through a formula. Please help?

[Justin_Barrett]

Formulas can only modify their own output. They can’t affect the contents of a multiple-select field, so the best you can do is create plain text.

Extending the formula from looking at one checkbox to looking at five is a matter of concatenating each output with the one before it using the & operator. The question then becomes, “How do we add separators (commas) without ending up with things like this?”

, , Sat Eve, ,

The answer is: add the commas last.

Here’s the test I made, copying your setup as closely as I could:

Here’s my formula:

SUBSTITUTE(TRIM(IF({Sat AM}, "Sat AM") & IF({Sat PM}, "  Sat PM") & IF({Sat Eve}, "  Sat Eve") & IF({Sun AM}, "  Sun AM") & IF({Sun PM}, "  Sun PM")), "  ", ", ")

From the inside out, there are three things going on:

1. I’m adding each label only if the associated checkbox is checked. Each option (except for the first one) begins with two spaces. This serves to help with both #2 and #3.
2. I then trim the result, which gets rid of any leading spaces (for cases where “Sat AM” isn’t the first checked option), leaving only the double-space gaps between any items.
3. I replace all instances of double spaces with a comma and space.