IF Statement that returns two values from two different columns
Good Morning Smartsheet Community,
Currently working on a formula that will take the information from "15 single select columns" and condense this information into a single text column. I am trying to reproduce the column titles in my new column as text within the cells.
Previous columns had a single selection for "1" if the issue was present, it would be left blank if no issue was present.
Current formula only produces the first value it finds and does not produce the second value if multiple columns were selected. Not sure how to get it to produce all the values that are present within the row.
Formula: =IF([Pass/Fail]@row = "Passed", " ", IF([Writing on Unit]@row = "1", "Writing on Unit", IF([Missing Parts]@row = "1", "Missing Parts", IF([Sharp Edges]@row = "1", "Sharp Edges", IF([Metal Finish]@row = "1", "Metal Finish", IF([Workmanship Quality]@row = "1", "Workmanship Quality", IF([Back Splash not at 90]@row = "1", "Back Splash not at 90", IF([Front Roll not Straight]@row = "1", "Front Roll not Straight", IF([Material Defects]@row = "1", "Material Defects", IF([Welding Issues]@row = "1", "Welding Issues", IF([Incorrect Parts]@row = "1", "Incorrect Parts", IF(Fitment@row = "1", "Fitment", IF([BOM Error]@row = "1", "BOM Error", IF([Dimensional Accuracy]@row = "1", "Dimensional Accuracy", IF([Engineering Issues]@row = "1", "Engineering Issues", IF([SpecFab Issues]@row = "1", "SpecFab Issues"))))))))))))))))
This formula only displays the first "Writing on Unit" it finds, rather than both a "Writing on Unit" and "Missing Parts" if both are present.
Thank you in advance for any help you could provide!
Best Answers
-
Because you are using a nested IF which stops on the first true value. What you would want instead would be a series of IF statements "added" together.
=IF([1st Column]@row = 1, "1st Column", "") + IF([2nd Column]@row = 1, "2nd Column", "") + ........................................
-
You would include it wherever you have a column name output.
For example:
"1st Column"
would change to
"1st Column "
Answers
-
Because you are using a nested IF which stops on the first true value. What you would want instead would be a series of IF statements "added" together.
=IF([1st Column]@row = 1, "1st Column", "") + IF([2nd Column]@row = 1, "2nd Column", "") + ........................................
-
Works perfectly, did not even consider adding the "+" into it.
My only other small issue is that it is displaying the text lumped together in one long string, instead of separating them with a space in between, is there a way I could structure this formula to accomplish the space between them?
Thank you again for the help Paul!
-
You would include it wherever you have a column name output.
For example:
"1st Column"
would change to
"1st Column "
-
WOW, it really is that easy for that one HAHA.
THANK YOU AS ALWAYS PAUL!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!