Prepopulating a value in a drop down box based on a formula

Jim Walker

I created a formula to prepopulate a value in a 'Status' field based on a 'Done' field being checked. For example. I have drop down status field with the following options: Completed, Not Started, In Progress, Incomplete. I have a separate checkbox field Called 'Done' . I set ups the following formula so that when the user checks Done that it autopopulates 'Completed' in the status (IF(Done@row=1,"complete"). The logic works great but it won't allow be to manually select the other options in the drop down (i.e not started, in progress, incomplete). It won't allow me to select other options if 'done' is checked or unchecked. Any idea how to apply this formula but also allow the user to select the other options as needed?

Mark Cronk

Hi @Jim Walker ,

If you use a formula for 1 selection you need to use a formula for all. Can you use the presence of a start date to trigger "In Process" and exceed a due date to trigger "In Complete"?

Mark

Jim Walker

So, I wouldn't be able to manually select if I also have a formula? If that's the case, I would need to default the value of 'Not Started' upon sheet creation. However, I would need the user to select ' In Progress' as that is subjective and not based on any other triggers in the sheet. Not possible?

Mark Cronk

The sheet admin and owner will be able to overwrite the formula by an editor will not.

If you want to use a formula to determine status you'll need to have a trigger for each state. The trigger could be a checkbox that it was stated. If none of your other triggers are present the stairs would be not stated.

Hope that helps.

Mark