Forms with formulas

Andrew Davidson

I'd like to include a formula (hidden) in a Form that includes a vlookup to another sheet with known email addresses. This would save the user time on entering their email when completing the form. The email is used to contact them back through the application to avoid sending separate emails. The formula is submitted to the sheet as expected but is always preceeded by a " ' " - see example and form image below:

'=VLOOKUP(Location1, {LU Range 1}, 3, false)

Andrée Starå

Hi,

You'd have to add at least two rows with the same formula so it will continue the pattern.

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

To expand on Andree's answer:

 

You cannot input a formula directly into a form. You would simply put the formula in the corresponding column on the sheet itself and not include that column/field on the form at all.

 

Provided you have 2 rows with the formula already in it, it will auto-populate any new rows with the formula and as a result provide the email address.

Andrée Starå

Paul,

I thought I wrote similar to you but no. I was thinking about it at least! 

Paul Newcome

Haha. I do that sometimes too. Especially if I get distracted in the middle of a response.

Andrée Starå

Yes, I know what - Squirrel - - - you mean!

Paul Newcome

HAHAHAHAHA!!!

Andrew Davidson

Got it. Thank you both. Realizing now you cannot use formulas in contact columns.

Andrée Starå

Happy to help!

Best,

Andrée

Paul Newcome

Happy to help. 

 

And that is correct. Depending on the overall size of your project and how much work you are willing to do towards setting it up, there are a couple of work-arounds.

 

You can submit a Product Enhancement Request to be able to use formulas within a contact column at this link:

https://app.smartsheet.com/b/form/739aa75f30ca43a8a22eb53e4da7d409?_ga=2.199971950.1717656072.1557144026-2072984282.1556800642

 

And in the mean time, if you would like some help with a possible solution, feel free to let us know. You've got a lot of knowledge with Andree on the thread, and I know a thing or two as well.

Kiat Umporn

Hello,

I have a similar problem. I have a sheet with several columns having column formula. When I build a form on it, the form just showed all columns but the formula columns. Trying to add a new field in the form with same field name as the formula column was rejected. I then added a new field in the form with different name, entered a formula in the corresponding column in the sheet(all the rows then have the same formula), the field was disappeared from the form. Need help, please

Andrée Starå

Hi @Jongkiat Anunta-Umporn

Not sure I follow.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Have a fantastic week & Happy Thanksgiving!

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!

Kiat Umporn

https://community.smartsheet.com/discussion/comment/270106#Comment_270106

Hi @Andrée Starå

I can now resolve the problem. If I enter a formula in a column and make it a 'column formula', then this column will be auto excluded from the form. Once I change the formula back to 'cell formula', then the column (field) is available to be included in the form.

Now, my next problem. Can I display a formula result in a form field? For example, I put an Employee Name in form field 1, and I want an Employee ID to auto populate in form field 2 (where I include a index/match formula in the corresponding sheet column. The sheet formula show the correct ID, but I cant make the form to show it.

Andrée Starå

@Kiat Umporn

Excellent!

Glad you solved it!

Regarding running formulas in a form.

Unfortunately, it's not possible at the moment, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment

✅Remember! 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!

Khaled Galal

Hi, is there a way to add a formula that only shows results on the form while adding the data?

Andrée Starå

Hi @Khaled Galal

Unfortunately, it's not possible now in Smartsheet Forms, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment

As a possible workaround, you could use a 3rd party form integration.

Is that an option?

I hope that helps!

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 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!