Keeping Track of Completion Dates and Updating Status
I am relatively new to using Smartsheet, but here is what I'd like to be able to do.
I have a Course Development Tracker sheet that lists courses to be developed in the next term. My team has a 60 day development process, with 5 checkpoints. Each checkpoint should be met by a certain number of days from the due date.
The team prefers having a drop down which they change to reflect the current checkpoint being worked on for the course:
BUS 123 Set to "Checkpoint #3" from the list in the drop-down.
I have a STATUS column with red, yellow, green, to indicating ON TRACK (yellow), NOT ON TRACK (red), NOT STARTED (gray), COMPLETED (green).
RED: User changes the drop down, and if not within the timeline, project is NOT ON TRACK.
YELLOW: User changes the drop down, and if within the timeline, project is ON TRACK.
GRAY: "Not Started" is selected in the drop down.
GREEN: "Project is completed.
Ideally, I would like for Smartsheet to take the TODAY() when the drop-down is changed, look at what is selected in the drop down, calculate the different from the total days available for the project, and return an update to STATUS.
Logically, I can see this, but I am not sure what the best approach would be in Smartsheet. I know how to put a formula in the STATUS column to change the color of the ball based on the value of the drop down, but this doesn't report that the progress is on track or not.