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

Jimrny · 08/29/22

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", ""))))),", ")

Paul Newcome · 08/30/22

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 · 08/31/22

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", "")))

Paul Newcome · 08/30/22

You would actually write out separate IF statements and "add" them together with the delimiter.

=IF(Response@row = 1, "Response, ") + IF(Reoccurring@row = 1, "Reoccurring, ") + so on and so forth...

Jimrny · 08/30/22

https://community.smartsheet.com/discussion/comment/341694#Comment_341694

@Paul Newcome , thank you! Another question I have is, if only 1 box is checked, is it possible to drop the delimiter?

It also shows a 0 when no boxes are checked, or when technician/set-up/equipment is selected alone.

But seems to be fine when all are selected

I'm using:

=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")

Jimrny · 08/30/22

=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", "")

Fixed my "0" issue, just trying to figure out the best way to have the delimiter drop if there's only one box checked. Any thoughts on this @Paul Newcome ?

Paul Newcome · 08/30/22

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", ""))

Jimrny · 08/31/22

https://community.smartsheet.com/discussion/comment/341749#Comment_341749

@Paul Newcome This worked out perfectly! Any chance you could answer this question about adding a pre-fix if the check boxes are true or not?

=IF(ISBLANK(Response@row:Equipment@row), "", "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", "")))

Paul Newcome · 08/31/22

@Jimrny I'm not sure I follow.

Jimrny · 08/31/22

https://community.smartsheet.com/discussion/comment/341868#Comment_341868

@Paul Newcome Sorry I mean, I was trying to display the text "MECHANICAL" in front of the joined values.

MECHANICAL - Response, Reoccurring, Equipment

I tried adding ="Mechanical" + String, but it leaves the value there if there are no check boxes selected.

Paul Newcome · 08/31/22

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", "")))

Jimrny · 08/31/22

https://community.smartsheet.com/discussion/comment/341899#Comment_341899

@Paul Newcome That makes sense to count the range with true values being greater than 0. Thank you so much!!

Paul Newcome · 08/31/22

Happy to help. 👍️

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

Best Answers

Answers

Help Article Resources

Categories