Could someone help me with this formula?
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!
Best Answer
-
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.
Answers
-
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.
-
Hi @MichaelTCA,
Thank you so much! This was super helpful! I can see myself using these formulas A LOT going forward.
Help Article Resources
Categories
=INDEX(ANCESTORS([Primary Column]@row), 1)<\/p>"},{"commentID":396977,"body":"
=IFFERROR(<\/strong>INDEX(ANCESTORS([Primary Column]@row), 1), [Primary Column]@row)<\/strong><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":110753,"type":"question","name":"If\/Then formula","excerpt":"I need to perform a calculation from 3 columns if criteria in a third column is met. What is the formula? Any help is appreciated!","snippet":"I need to perform a calculation from 3 columns if criteria in a third column is met. What is the formula? Any help is appreciated!","categoryID":322,"dateInserted":"2023-09-25T21:29:24+00:00","dateUpdated":null,"dateLastComment":"2023-09-26T17:35:30+00:00","insertUserID":166870,"insertUser":{"userID":166870,"name":"Kelly Cepicky","title":"Director Operations","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Cepicky","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-26T17:33:58+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":166870,"lastUser":{"userID":166870,"name":"Kelly Cepicky","title":"Director Operations","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Cepicky","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-26T17:33:58+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":39,"score":null,"hot":3391431894,"url":"https:\/\/community.smartsheet.com\/discussion\/110753\/if-then-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110753\/if-then-formula","format":"Rich","lastPost":{"discussionID":110753,"commentID":397031,"name":"Re: If\/Then formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397031#Comment_397031","dateInserted":"2023-09-26T17:35:30+00:00","insertUserID":166870,"insertUser":{"userID":166870,"name":"Kelly Cepicky","title":"Director Operations","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Cepicky","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-26T17:33:58+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-09-26T17:35:04+00:00","dateAnswered":"2023-09-26T16:29:13+00:00","acceptedAnswers":[{"commentID":397001,"body":" Totally possible, though the formula might not necessarily be thought of as \"quick\"! This is also only taking months into account, so it's only looking at the month number and disregards the day.<\/p> If 2023 is your baseline:<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/5BCTN4WOU43P\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
