Formula to prevent wrong input


I need a bit of further help. I have two columns A & B for inputting numbers. A is the upper limit and B the lower limit. They are both required to be filled. It is quite possible that the persons inputting the data may inadvertently put the lower value in the upper limit column (A) and visa versa. Can a formula be created to prevent this from happening?


  • The numbers are inputting on a form.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 03/19/24

    Hi @Vinton Douglas

    In Smartsheet, you can't directly prevent users from entering incorrect values via a form using a formula, you can create a formula in your intake sheet using helper column to flag entries where the value intended for the upper limit (column A) is lower than the value for the lower limit (column B). This way, you can easily identify incorrect entries and take action accordingly.

    Here's a simple approach to flagging these instances:

    1. Create a new column (let's call it "Check Limits").
    2. In the first row of the "Check Limits" column, enter the following formula and convert it to column format formula:
    =IF(AND(A@row <> "", A@row <> ""), IF( A@row < B@row, "Incorrect", "Correct"), "")

    then you can create an automation to notifying you about wrong inputs using the "Check Limits" helper column in your automaton.

    PMP Certified

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Scott Orsey
    Scott Orsey ✭✭✭✭✭

    Hi Vinton, No... there isn't a way to validate the input on a form beyond ensuring things like that it's a number or email. You could create a formula on the sheet (that would calculate once the form is submitted) to flag the input error. From there you could do anything from highlight the cells with conditional formatting to using an automation to notify someone.

    Hope this helps.

    Be well

    If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!