Showing multiple true checkboxes as joined or concatenated text in another column

KateP721
KateP721 ✭✭✭
edited 12/09/19 in Formulas and Functions

I have a volunteer submission form which has about 10 columns that exist as just checkboxes on/off - each of these relate to a conference work area and volunteers often select multiple.

To easily view the data for a single person/row without tons of scrolling and looking up and down at column titles, I want to aggregate all their checked ("true" or "1") boxes into one cell, but instead of showing true/false, show the name of the column.

After extensive searching in the community, I was able to make some headway and got the first two columns to work as expected, but as soon as I add another column, the formula breaks. I feel as if I've copied/pasted this every which way I can.

For the row 5 volunteer, as an example, they've checked off Auctions and Awards...

THIS WORKS

=IF([Action Team: Association Business]5 = 1, [Action Team: Association Business]$1 + ", ") + IF([Action Team: Auctions]5 = 1, [Action Team: Auctions]$1 + ", ")

and produces the desired result: Auctions,

And if I check Association Business, it will add that before Auctions, as expected.

HOWEVER, as soon as I add the next column, the formula breaks.

THIS DOES NOT WORK:

=IF([Action Team: Association Business]5 = 1, [Action Team: Association Business]$1 + ", ") + IF([Action Team: Auctions]5 = 1, [Action Team: Auctions]$1 + ", ") + IF([Action Team: Awards]5 = 1, [Action Team: Awards]$1 + ", ")

 

ANY ADVICE? I attempted putting my IF statements in parentheses and that didn't help. Should I be using a JOIN or COLLECT function instead? I attempted that and using SUBSTITUTE but it was getting quite complex and my head about exploded :-)

Help please! Thank you!!

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!