Automating at Risk Flag

Caitlin Goodman
Caitlin Goodman ✭
inFormulas and Functions

Hi All,

I'm a total newbie to Smartsheets and need some help.

I'm trying to automate my At Risk Flag. I need it to flag projects a month from their due date if they are 50% or less complete.

Thank you!

· Share on Facebook Share on Twitter

Best Answer

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
1 I have tried to specifically say, do not count anything with CDA in it, in a variety of…","snippet":"I am using [Method helper] to tell me when we have more than one online training on a date where Notes= PD =COUNTIFS(Notes:Notes, \"PD\", [Date 1]:[Date 1], [Date 1]@row,…","categoryID":322,"dateInserted":"2023-10-20T13:35:21+00:00","dateUpdated":null,"dateLastComment":"2023-10-20T14:12:35+00:00","insertUserID":164920,"insertUser":{"userID":164920,"name":"ECRCalexandra","title":"Data Manager","url":"https:\/\/community.smartsheet.com\/profile\/ECRCalexandra","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-20T14:09:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":164920,"lastUser":{"userID":164920,"name":"ECRCalexandra","title":"Data Manager","url":"https:\/\/community.smartsheet.com\/profile\/ECRCalexandra","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-20T14:09:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":20,"score":null,"hot":3395621876,"url":"https:\/\/community.smartsheet.com\/discussion\/111953\/countifs-counting-this-not-in-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111953\/countifs-counting-this-not-in-criteria","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":111953,"commentID":400943,"name":"Re: Countifs counting this not in criteria","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400943#Comment_400943","dateInserted":"2023-10-20T14:12:35+00:00","insertUserID":164920,"insertUser":{"userID":164920,"name":"ECRCalexandra","title":"Data Manager","url":"https:\/\/community.smartsheet.com\/profile\/ECRCalexandra","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-20T14:09:06+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/A4TLEFFGIFXV\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/A4TLEFFGIFXV\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/A4TLEFFGIFXV\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/A4TLEFFGIFXV\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/A4TLEFFGIFXV\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/A4TLEFFGIFXV\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-20T14:13:01+00:00","dateAnswered":"2023-10-20T13:45:27+00:00","acceptedAnswers":[{"commentID":400923,"body":"

Hi @ECRCalexandra<\/a>, <\/p>

If you're not wanting to flag the non-PD lines, then use something along the lines of this:<\/p>

=IF(AND(Notes@row = \"PD\", COUNTIFS(Notes:Notes, \"PD\", [Date 1]:[Date 1], [Date 1]@row, Platform:Platform, Platform@row, Platform:Platform, \"online\") > 1), 1, 0)<\/p>

This does the checks, but makes sure the line is PD before ticking\/not.<\/p>

Hope this helps, but if you've any problems\/questions or I've misunderstood something then just post! 🙂<\/span><\/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":111950,"type":"question","name":"Using COUNTIF(S) with RIGHT functionality","excerpt":"Hello! We have a record ID number that includes text. The first handful of records were entered manually for historical purposes. Now, records coming through the Smartsheet intake form are automated to begin with the number 100. For the dashboard, I'm looking to only count records with an ID greater than 100. This doesn't…","snippet":"Hello! We have a record ID number that includes text. The first handful of records were entered manually for historical purposes. Now, records coming through the Smartsheet intake…","categoryID":322,"dateInserted":"2023-10-20T13:05:21+00:00","dateUpdated":null,"dateLastComment":"2023-10-20T13:46:21+00:00","insertUserID":165889,"insertUser":{"userID":165889,"name":"mónicas","url":"https:\/\/community.smartsheet.com\/profile\/m%C3%B3nicas","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-20T14:11:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-20T14:33:39+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":21,"score":null,"hot":3395618502,"url":"https:\/\/community.smartsheet.com\/discussion\/111950\/using-countif-s-with-right-functionality","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111950\/using-countif-s-with-right-functionality","format":"Rich","lastPost":{"discussionID":111950,"commentID":400924,"name":"Re: Using COUNTIF(S) with RIGHT functionality","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400924#Comment_400924","dateInserted":"2023-10-20T13:46:21+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-20T14:33:39+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-10-20T13:38:04+00:00","dateAnswered":"2023-10-20T13:26:51+00:00","acceptedAnswers":[{"commentID":400907,"body":"

Hi @mónicas<\/a>,<\/p>

Something like this should do the trick:<\/p>

=COUNTIF({Record ID 3}, RIGHT(@cell, 3) >= 100)<\/p>

Hope this helps, but if you've any problems\/questions then just post! 🙂<\/span><\/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":[]},{"discussionID":111945,"type":"question","name":"Task completion (%)","excerpt":"Hello everyone, is there a way to set the % complete based on the task status? I have a table [state ; % complete] I tried with \"VLOOKUP\" function but isn't possible to insert formulas in the field. Help me please! Thanks","snippet":"Hello everyone, is there a way to set the % complete based on the task status? I have a table [state ; % complete] I tried with \"VLOOKUP\" function but isn't possible to insert…","categoryID":322,"dateInserted":"2023-10-20T08:08:10+00:00","dateUpdated":null,"dateLastComment":"2023-10-20T12:12:07+00:00","insertUserID":167621,"insertUser":{"userID":167621,"name":"malanto","url":"https:\/\/community.smartsheet.com\/profile\/malanto","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtdOU4y0c3qfFcSspkESXLsc4e82hT4Gg2MEm5ptKEc3=s96-c","dateLastActive":"2023-10-20T14:37:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":167621,"lastUser":{"userID":167621,"name":"malanto","url":"https:\/\/community.smartsheet.com\/profile\/malanto","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtdOU4y0c3qfFcSspkESXLsc4e82hT4Gg2MEm5ptKEc3=s96-c","dateLastActive":"2023-10-20T14:37:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":43,"score":null,"hot":3395598617,"url":"https:\/\/community.smartsheet.com\/discussion\/111945\/task-completion","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111945\/task-completion","format":"Rich","tagIDs":[254,440,595],"lastPost":{"discussionID":111945,"commentID":400896,"name":"Re: Task completion (%)","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400896#Comment_400896","dateInserted":"2023-10-20T12:12:07+00:00","insertUserID":167621,"insertUser":{"userID":167621,"name":"malanto","url":"https:\/\/community.smartsheet.com\/profile\/malanto","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtdOU4y0c3qfFcSspkESXLsc4e82hT4Gg2MEm5ptKEc3=s96-c","dateLastActive":"2023-10-20T14:37:59+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-10-20T09:14:44+00:00","dateAnswered":"2023-10-20T08:22:14+00:00","acceptedAnswers":[{"commentID":400877,"body":"

Hi @malanto<\/a>,<\/p>

This is possible, but you would need to define the criteria. <\/p>

You would use a nested IF statement. A very basic example:<\/p>

Status % Complete<\/p>

Not Started 0<\/p>

In Progress 50<\/p>

Complete 100<\/p>

Would have the following formula in the % Complete column:<\/p>

=IF([Status]@row=\"Not Started\",0,IF([Status]@row=\"In Progress\",0.5,IF([Status]@row=\"Complete\",1,\"//www.santa-greenland.com/community/discussion/73570/\")))<\/p>

This assumes you've set the column to be %, otherwise you'd want to increase the numbers by 100 (0.5 to 50).<\/p>

Hopefully this gives you some ideas, but if you're still unsure or have any problems\/questions then just ask! 🙂<\/span><\/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"},{"tagID":440,"urlcode":"project-management","name":"Project Management"},{"tagID":595,"urlcode":"help","name":"help"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Countifs counting this not in criteria

    I am using [Method helper] to tell me when we have more than one online training on a date where Notes= PD =COUNTIFS(Notes:Notes, "PD", [Date 1]:[Date 1], [Date 1]@row, Platform:Platform, Platform@row, Platform:Platform, "online") > 1 I have tried to specifically say, do not count anything with CDA in it, in a variety of…

  • Using COUNTIF(S) with RIGHT functionality

    Hello! We have a record ID number that includes text. The first handful of records were entered manually for historical purposes. Now, records coming through the Smartsheet intake form are automated to begin with the number 100. For the dashboard, I'm looking to only count records with an ID greater than 100. This doesn't…

  • Task completion (%)

    Hello everyone, is there a way to set the % complete based on the task status? I have a table [state ; % complete] I tried with "VLOOKUP" function but isn't possible to insert formulas in the field. Help me please! Thanks