Lookup multiple columns for text value and return cell value

ICue

I'm trying to figure out a formula to return the value in a cell over multiple columns. I have 10 locations consisting of various buildings for each location. Only one column will have text in it. The formula I have will obviously only return the text for that column and I cannot figure out how to repeat the formula to check all 10 columns. Help please!

This is what is working for one column. Is there a way to repeat it for all?

=IF(ISTEXT([Main Campus Buildings]@row), [Main Campus Buildings]@row).

Thanks!

Paul Newcome

Are all 10 columns next to each other? If so you could use...

=JOIN(COLLECT([First Column]@row:[Last Column]@row, [First Column]@row:[Last Column]@row, @cell <> ""))

ICue

Yes, thankfully they are all next to each other and your formula worked perfectly. Thank you so much!!!

Paul Newcome

Happy to help! 👍️

Kristin Kornchuk

Would this formula work in a form referencing a Item# (far left of sheet) then looking across several columns (all next to each other in the far right of the sheet) to return a value based on the column name? Example: Item # KLK2 for "Inter to LMJC" returns the "Inter" value into the form field? Thanks!

Paul Newcome

@Kristin Kornchuk Are you able to provide a "mocked up" screenshot to show what you are wanting to accomplish?

Kristin Kornchuk

Sure. In the form based on the sheet, if the user selects the Item # in col A or Vendor Part # is col C (or some other identifier) in combination with selecting one of the choices in col E - H, return the correct numerical value from the second row to the form field. Is that possible?

Paul Newcome

When building a form, there is conditional logic that you can build in, but I haven't played with it enough to know whether or not you can get a field to display specific data or if it is just showing or hiding fields based on selections.

Other than that possibly being something to look at, you cannot have fields in a form populate via formula while it is being filled out.

You can have it auto-populate in the sheet after the form is filled out though if that would be an option.

Kristin Kornchuk

Paul, do you know if this has been implemented in Smartsheet?

https://community.smartsheet.com/discussion/23311/conditional-logic-in-forms

Andrée Starå

Hi @Kristin Kornchuk

Yes, it’s released, but maybe it isn’t exactly what you thought it would be.

How would you like to use it?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

It has been released, but I don't think it is going to work exactly how you are wanting. There may be a solution using a form with an Update Request follow-up though.

Peter G.

@Paul Newcome Just what I was looking for!!!!!