Multiple Check Boxes - Pre-Fix, joining values based on multiple check boxes.

Options
Jimrny
Jimrny ✭✭
edited 08/29/22 in Formulas and Functions

What would be the best formula to join "text" from multiple check boxes if values are true or false?

I would like to populate, the in the grey column, the values of each check box if any are true with a "text value" of the name of the check box column.


I have several grey columns that will be used in with join/collect on another sheet.


I've tried this, but seems to be incorrect:


=JOIN(IF(Response@row = 1, "Response", IF(Reoccurring@row = 1, "Reoccurring", IF(Technician@row = 1, "Technician", IF(Set-Up@row = 1, "Set-Up", IF(Equipment@row = 1, "Equipment", ""))))),", ")

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this one:

    IF the COUNTIFS of checkboxes is 1 then SUBSTITUTE the comma with a blank, otherwise run the full string.


    =IF(COUNTIFS(Response@row:Equipment@row, @cell = 1) = 1, SUBSTITUTE(IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", ""), ",", ""), IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", ""))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Give this a go...


    =IF(COUNTIFS(Response@row:Equipment@row, @cell = 1) > 0, "MECHANICAL - " + IF(COUNTIFS(Response@row:Equipment@row, @cell = 1) = 1, SUBSTITUTE(IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", ""), ",", ""), IF(Response@row = 1, "Response, ", "") + IF(Reoccurring@row = 1, "Reoccurring, ", "") + IF(Technician@row = 1, "Technician, ", "") + IF([Set-Up]@row = 1, "Set-Up, ", "") + IF(Equipment@row = 1, "Equipment", "")))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!