Change Status if Date Completed is not blank

rose.yoon37366

Our team has a sheet to track all of our tasks - who it's assigned to, when it's due, date it's completed, the status (requested, assigned, complete), etc.

We have people updating the "Date Completed" column, but forgetting to update the "Status" column to "Complete" (The "Status" column is a drop down column). Is there a way to change the Status to "Completed" if there is a date in the "Date Completed" column (cell is not blank)?

 

Appreciate it!

Mike Wilday

You could write a formula that does that, but if users are using reports to change the status they wouldn't be able to edit the status, because formulas disable the ability to edit a field in a report. Also, if a user used a dropdown to change the status to something other than its initial setting of the formula, then the formula would be overwritten and otherwise useless. 

You will want to determine if you want all the status' automated or none. Then you would want to determine a method for indicating when the status was "in Progress" if you are using that status. 

Geoff Kittmer

@rose.yoon37366 How did you end up resolving this? I have a sheet that I want to mark as completed once a finish date is populated.

@Mike Wilday when you say write a formula, so you get rid of the drop down option? We have eliminated percentages and were just using the prefilled status of Not Started, In Progress and Complete. So was hoping that they can still use it, but if they put a date in then it will just automatically convert to completed.

Mike Wilday

Smartsheet just released a change a value automation. Try it out. You can have it change the status to complete when the date column is edited.

Here is the article on it:

https://help.smartsheet.com/articles/2482299-change-cell-value-in-an-automated-workflow