Change Status if Date Completed is not blank

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!

Find more posts tagged with

Conditional Formatting

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Does the change in value within a formula not count as a change?
I'm trying to create an automation. In this automation, I want to copy rows when a value changes based on a formula (a count from another sheet), but the automation isn't triggering. What could be causing this issue?
Dropdown list capabilities
Good day, Is it possible to link a form drop-down list to a column in a sheet, so that the drop-down values update automatically whenever that column changes? In other words, if items are added or removed in the column, the form’s drop-down list would update without needing manual adjustments.
Why isn't the cog wheel "Edit project settings" visable in grid view?
When working in a sheet, why isn't the cog wheel "Edit project settings" visable in grid view?