Could someone help me with this formula?

Estephania

We are asking our managers to use SmartSheet to enter information about their employees (via the add form). Be are requiring them to enter the employee ID which has to be 9 digits, but many times they don't enter all the numbers. I read about helper columns to check whether or not the format is accurate and then send an alert/update request after the fact. I know how to set up the alert/update request, but I need help with the formula in the helper column to check whether the format is correct or not. Could someone help me with this formula?

Or if there is already a way to force a format, that would be much better!

MichaelTCA

Hello @Estephania

You can use a checkbox as your helper column and trigger.

The function will be verifying the length of the value and the box will be checked if it is too short and too long, or unchecked if it is correct.

We will use the functions LEN and IF.

=IF(LEN([EmployeeID]@row)=9,0,1)

The checkbox is a variable that only recognizes true or false results. 0 means False (the ID is the correct length) and 1 means True (the ID is incorrect).

You can also use =IF(LEN(EmployeeID]@row<>9,1,0) if it's easier for you to recognize. The results will be exactly the same.

MichaelTCA

Hello @Estephania

You can use a checkbox as your helper column and trigger.

The function will be verifying the length of the value and the box will be checked if it is too short and too long, or unchecked if it is correct.

We will use the functions LEN and IF.

=IF(LEN([EmployeeID]@row)=9,0,1)

The checkbox is a variable that only recognizes true or false results. 0 means False (the ID is the correct length) and 1 means True (the ID is incorrect).

You can also use =IF(LEN(EmployeeID]@row<>9,1,0) if it's easier for you to recognize. The results will be exactly the same.

Estephania

Hi @MichaelTCA,

Thank you so much! This was super helpful! I can see myself using these formulas A LOT going forward.