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)

smartsheet.JPG

«1

Comments

  • Andrée Starå
    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    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.

    thinkspi.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Paul,

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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    thinkspi.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Yes, I know what - surpriseSquirrel - - - you mean!laugh

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    HAHAHAHAHA!!!

    thinkspi.com

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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. yes

     

    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.

    thinkspi.com

  • 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å
    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, [email protected])

    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: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Khaled Galal
    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å
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Help Article Resources