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)
Comments
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
Paul,
I thought I wrote similar to you but no. I was thinking about it at least!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Haha. I do that sometimes too. Especially if I get distracted in the middle of a response.
-
Yes, I know what - Squirrel - - - you mean!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
-
Got it. Thank you both. Realizing now you cannot use formulas in contact columns.
-
Happy to help!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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:
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.
-
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
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi, is there a way to add a formula that only shows results on the form while adding the data?
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 494 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!