Formula to auto-summarise Delivery Risk assessment
Hi all,
I'm attempting to build a function into my master milestone tracking sheet which will auto-populate my ‘Delivery Risk’ column, based on a series of conditions met in other cells in the row. Underpinning thinking is as follows:
- ‘Target Milestone Date’ are set for every row
- Assumption that ‘Actual Completion Date’ will be blank for each ‘Delivery Risk’ condition
- The ‘Progress’ column is manually updated by project leads based on, surprisingly, progress
- The only condition where this isn’t the case, is when an ‘Actual Completion Date’ is entered, which runs an automation to change ‘Progress’ to ‘Complete’
- Therefore, I’ve been trying to create a formula using =IF(AND(ISBLANK etc to address the logical conditions listed in the table below, but have been struggling with the complexity of the conditions
- ‘Status’ auto populates based on conditional formating
Any help or advice in how to set this formula up would be much appreciated.
Thanks,
Guy
Best Answer
-
This formula should do the trick for you
=IF(AND([Target Milestone Date]@row - TODAY() > 14, Progress@row = "Not Started"), "On Track", IF(AND([Target Milestone Date]@row - TODAY() > 7, Progress@row = "In Progress"), "On Track", IF(AND([Target Milestone Date]@row - TODAY() < 14, [Target Milestone Date]@row - TODAY() > 6, Progress@row = "Not Started"), "At Risk", IF(AND([Target Milestone Date]@row - TODAY() < 7, Progress@row = "In Progress"), "At Risk", IF(AND([Target Milestone Date]@row - TODAY() < 7, Progress@row = "Not Started"), "Behind", IF(AND([Target Milestone Date]@row <= TODAY(), ISBLANK(Progress@row)), "Behind", IF(Progress@row = "Complete", "", "Error")))))))
Tested and working as a column formula
Hope that helps
Thanks
Paul
Answers
-
This formula should do the trick for you
=IF(AND([Target Milestone Date]@row - TODAY() > 14, Progress@row = "Not Started"), "On Track", IF(AND([Target Milestone Date]@row - TODAY() > 7, Progress@row = "In Progress"), "On Track", IF(AND([Target Milestone Date]@row - TODAY() < 14, [Target Milestone Date]@row - TODAY() > 6, Progress@row = "Not Started"), "At Risk", IF(AND([Target Milestone Date]@row - TODAY() < 7, Progress@row = "In Progress"), "At Risk", IF(AND([Target Milestone Date]@row - TODAY() < 7, Progress@row = "Not Started"), "Behind", IF(AND([Target Milestone Date]@row <= TODAY(), ISBLANK(Progress@row)), "Behind", IF(Progress@row = "Complete", "", "Error")))))))
Tested and working as a column formula
Hope that helps
Thanks
Paul
-
Hi Paul,
Amazing, this looks to be working great. Thank you very much!
Guy
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!