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!

Tags:

• ✭✭✭✭✭✭

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

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

Regards,

Jeff Reisman

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

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!