Forms with formulas

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)

Find more posts tagged with

Forms

Formulas

Support

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

index match using multiple critieria
Hello, I want to use a formula using INDEX/ MATCH Function using multiple criteria to get a single value I am trying to get a value of Column Description from File 2 based on matching Source System ,Table name and Attribute Field Index ( as the column names may be different ) The formula Im using gets me an INVALID…
How can I use Conditional Formatting for date in the past or future when a formula created the date
Hi Everyone, Thank you for your time in reading this. How can I use Conditional Formatting for date in the past or future when a formula created the date? The black columns are different dates generated. The 2 yellow columns are then selecting the relevant date based on different criteria. The white columns are me trying…
formula for raid log
I would like a formula that if a user selects risk in the type column and enters the risk information.. and later on in the project this risk becomes an issue.. the formula will auto score the likelihood column to default to 5. Can anyone help with this please?