Formula to pull the non-blank value from multiple columns

Kyle Cannella

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!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/315600#Comment_315600

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

Jeff Reisman

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.

JOIN Function | Smartsheet Learning Center

Kyle Cannella

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?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/315600#Comment_315600

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

Kyle Cannella

That seemed to fix it, thanks for the help Jeff!