Formula to pull the non-blank value from multiple columns
Not sure if this is possible but I'm trying to pull a non-blank value from one of multiple columns and paste it in a separate column. Essentially, there are 5 columns that are Department (Deburr, Lathe, Mill), Deburr Machine, Lathe Machine, Mill Machine, and machine (think of this column as the master column, or combination of all machines). In a form with conditional logic, the Department is selected and then displays the available values for the chosen machine. For the example, Deburr can only have value A, Lathe can only have B, Mill can only have C (achieved via single select dropdown). From here, the value of the chosen machine is selected and the two other machines are left blank or can be set to something like NULL or BLANK via automation if needed.
What I'm trying to achieve is pasting the value chosen under the Deburr/Lathe/Mill Machine columns into the master machine column. I've attached a picture below that shows how the sheet would work. I've tried some COUNTIF formulas but I can't seem to get it to work. Is this possible or should I abandon this idea?
Thanks for the help!
Best Answer
-
Interesting.
You can try outputting the result of the JOIN to text by adding +"" (plus sign and two " " quotes) after the formula. The result should be a plain text string.
=JOIN([Deburr Machine (Hide)]@row:[Mill Machine (Hide)]@row) +""
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
If you will only have one of the three columns populated per row, then it's just a matter of using the JOIN function in the machine column:
=JOIN([Deburr Machine (Hide)]@row:[Mill Machine (Hide)]@row)
This just joins the values from the three columns together into a text string.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
It works, however it is negatively interacting with a different function I have because the output is a unique string rather than the basic text value if that makes sense. Is there a way of doing this outside of the Join function?
-
Interesting.
You can try outputting the result of the JOIN to text by adding +"" (plus sign and two " " quotes) after the formula. The result should be a plain text string.
=JOIN([Deburr Machine (Hide)]@row:[Mill Machine (Hide)]@row) +""
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
That seemed to fix it, thanks for the help Jeff!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 202 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!