Alert when tasks are planned to finish after a date in another task (deadline)

NBookbinder
NBookbinder ✭
inSmartsheet Basics

Hello all! I am trying to program a sheet to alert users when any finish date in the sheet goes beyond a specific deadline task in the sheet.

I foresee it working like: User changes a start date, and several tasks down the dependency waterfall, a finish date gets pushed beyond the deadline task's date, and an error shows up requiring the dates to be pulled up or the deadline to be moved out for the date to be changed. Has anyone been able to setup a formula that could enable this?

Tags:
· Share on Facebook Share on Twitter

Answers

  • DKazatsky
    DKazatsky ✭✭✭

    Hi@NBookbinder

    Here is one way...

    Add a column that is a checkbox called "Past Deadline?" or whatever you choose. In that column use a formula that sets the checkbox: =IF([End Date]@row > [Deadline Date]$1, 1) and then create an alerting workflow that is based on the "Past Deadline?" field being checked.

    **NOTE: The forlmula uses a cell reference "[Deadline Date]$1" so cannot be turned into a column formula. You will need to drag this formula, or cut-and-paste, into every row.

    Hope this helps,

    Dave

    · Share on Facebook Share on Twitter

Categories

What type of plan are you on? Certain plan types require that a system admin give you permission to access group management.<\/p>


<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":109197,"type":"question","name":"Forms","excerpt":"I created two different forms and the data entered on both of them is saved in the same sheet. How do I get it to save on two separate sheets?","snippet":"I created two different forms and the data entered on both of them is saved in the same sheet. How do I get it to save on two separate sheets?","categoryID":321,"dateInserted":"2023-08-21T14:53:07+00:00","dateUpdated":null,"dateLastComment":"2023-08-21T21:46:32+00:00","insertUserID":165409,"insertUser":{"userID":165409,"name":"mpatton","url":"https:\/\/community.smartsheet.com\/profile\/mpatton","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zDPQszzjyv0!iTHSYpyQ8H0!4mIm1J-gpMN","dateLastActive":"2023-08-21T23:16:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-21T22:29:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":25,"score":null,"hot":3385286979,"url":"https:\/\/community.smartsheet.com\/discussion\/109197\/forms","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109197\/forms","format":"Rich","lastPost":{"discussionID":109197,"commentID":391727,"name":"Re: Forms","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391727#Comment_391727","dateInserted":"2023-08-21T21:46:32+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-21T22:29:51+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-21T20:23:16+00:00","dateAnswered":"2023-08-21T19:38:23+00:00","acceptedAnswers":[{"commentID":391698,"body":"

You would create the sheet first, then you can click on \"Forms\" in the top right corner of the new sheet to create the form.<\/p>


<\/p>

If you need a bunch of the same columns, You can \"Save as new\" the existing sheet and then just delete whatever you don't need from each sheet.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":109205,"type":"question","name":"Auto Populate Column Data","excerpt":"I have a sheet that I am populating row one with data inputted using an intake form (Form1). I then have a Update form (Form2) that is used by end users to input additional data. I need the data inputted from form1 to auto populate the entire column the data resides in when form2 is submitted. Any suggestions on how to…","snippet":"I have a sheet that I am populating row one with data inputted using an intake form (Form1). I then have a Update form (Form2) that is used by end users to input additional data.…","categoryID":321,"dateInserted":"2023-08-21T16:13:10+00:00","dateUpdated":null,"dateLastComment":"2023-08-21T19:37:00+00:00","insertUserID":162922,"insertUser":{"userID":162922,"name":"MSGFreebird","title":"","url":"https:\/\/community.smartsheet.com\/profile\/MSGFreebird","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtfyipG4v_0PcUXERFavMo9q8-BAK6RWzljovF2sarPztFY=s96-c","dateLastActive":"2023-08-21T22:54:52+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-21T22:29:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":39,"score":null,"hot":3385285210,"url":"https:\/\/community.smartsheet.com\/discussion\/109205\/auto-populate-column-data","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109205\/auto-populate-column-data","format":"Rich","lastPost":{"discussionID":109205,"commentID":391697,"name":"Re: Auto Populate Column Data","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391697#Comment_391697","dateInserted":"2023-08-21T19:37:00+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-21T22:29:51+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/QSNSANMKG9JV\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-21T18:39:09+00:00","dateAnswered":"2023-08-21T18:00:47+00:00","acceptedAnswers":[{"commentID":391672,"body":"

Yes. The \"@row\" piece of the formula is looking for the cell within the row of the formula. So on any row where the meta columns are blank, \"@row\" will also return blank.<\/p>


<\/p>

Try this instead:<\/p>

=INDEX([AT-Meta]:[AT-Meta], 1)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=321&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":4964,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Smartsheet Basics

  • Creating a contact group in Smartsheet?

    嗨,我想创建一个联络小组(即组织p of collaborator emails). I came across a Help post that instructs me to click on the Account Icon and select "Group Management", however, when I click on the icon, I don't have that option. If I select "My Smartsheet Contacts", there is also no option to create contact…

  • Forms

    I created two different forms and the data entered on both of them is saved in the same sheet. How do I get it to save on two separate sheets?

  • Auto Populate Column Data

    I have a sheet that I am populating row one with data inputted using an intake form (Form1). I then have a Update form (Form2) that is used by end users to input additional data. I need the data inputted from form1 to auto populate the entire column the data resides in when form2 is submitted. Any suggestions on how to…

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video