Dropdown Formula

tomrod16

I have two columns, Status and Done.  Status is a drop down with the options: Backlog, Working On It, Fast Tracking, Waiting on Someone, Done.  The Done column is a checkbox, 1 or 0.  I have already inputted the logic where when the subtasks are "done" (1), the parent automatically switches to "done".  Here's that formula: =IF(COUNT(CHILDREN(Done1)) = COUNTIF(CHILDREN(), 1), 1, 0)

Now what I want to do is have the drop down automatically change to done when this happens, but also revert back to where it was if a subtask gets unchecked.  I was able to get it to change to done with: =IF(Done1 = 1, "Done"), but if I unclick a subtask, it becomes an empty cell.  

Help Please?

 

J. Craig Williams

Smartsheet has no toggle or trigger feature. That is, there is a feature to remember a previous state.

Trying to fool it with various additional columns or hidden cells won't cut it either, as you will run into circular reference errors.

Lastly, once you manually update the dropdown (if you do), then the formula is gone for good.

If you can have the [Status] column be determined by formula, then that would be my suggestion.

Craig