Formula to prevent wrong input

Vinton Douglas

Hello,

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?

Vinton Douglas

The numbers are inputting on a form.

Bassam Khalil

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.

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