Multiple Check Boxes - Pre-Fix, joining values based on multiple check boxes.
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
-
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", ""))
-
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
-
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...
-
@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")
-
=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 ?
-
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 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 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.
-
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 That makes sense to count the range with true values being greater than 0. Thank you so much!!
Help Article Resources
Categories
Check out the Formula Handbook template!
