- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 26, 2020 02:13 PM
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:
- I’m still in the experimentation phase to see which formats work best
- I need the info to be sortable/filterable/analyzable in many different views
- 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?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 26, 2020 11:37 PM
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:
- 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.
- 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.
- I replace all instances of double spaces with a comma and space.