formula to combine based on form submissions

mkhue

I've built a form that asks a user to manually fill in 1 or 2 fields based on their desired job title for their email signature. Here are the choices:

From the top down, let's call them options 1, 2, 3, and 4.

If they choose option 2, the form logic will populate two text boxes for them. The first box will ask for which Team and the second box will ask for which Office. Similarly if they chose option 1, it would only have one box asking which Team.

Now the question is how do I combine the data based on their choice? Is this possible using a function like ISBLANK or NOT(ISBLANK)? When I was doing some googling trying to find a solution, I found that this isn't possible (yet) using the automation since we can't use tags like we can in email alerts (like {{example}} ).

I understand the formula can be large and unwieldy but that'll still be better than trying to manually combine it after we gather the data. Thanks!

Andrée Starå

Hi @mkhue

I hope you're well and safe!

Not sure I follow! Can you elaborate?

It sounds like it should be possible to use a formula in the sheet to create what you need.

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

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

mkhue

Hi Andree, sure.

Let's say I choose option 2 when filling out the form. The form will then ask which Team? Let's say I type in "Awesome" for this example. Then there will be another question asking which Office? Let's say I enter "New York".

Now on my smartsheet, I have 3 data points. The user chose Option 2 "Chair of _____ Team - _____ Office", "Awesome", and "New York".

The next user chooses option 1 "Chair of _____ Team". They will then get asked which Team since there is no specific Office label like option 2. Let's say they enter "Spectacular". We now have 2 data points in 2 other columns on smartsheet for user 2.

What's a formula to check which option they chose and then combine it with their data inputted? So user 1 will have a title that says "Chair of Awesome Team - New York Office" and user 2 will have a title that says "Chair of Spectacular Team".

Andrée Starå

@mkhue

If you put the columns beside each other and add columns with the text between, you could use a JOIN(COLLECT formula combination to get the desired result. You need to tweak it, so the text changes depending on the selections.

Make sense?

Would that work?

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