Skip to main content

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?

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.


Reply


Terms & Conditions