CheckBox Formula

02/12/21
Answered - Pending Review

Greetings!

So, I have a "Done" column and a "Status" column.

Status column values = Not Started , In Progress, Complete

Done value = Checkbox

The Goal:

When I check the "Done", I would like to have the Status column show Complete. I have partially achieved this with the following formula: =IF([email protected] = 1, "Complete")

However, if a manual value is inserted first, this formula does not work.

Is there a way, maybe with conditional formatting, to override the manual value of the Status column and always set to "Complete" when the checkbox is checked?


Thanks in advance!

Answers

  • Mike WildayMike Wilday ✭✭✭✭✭

    Actually, this is not possible. Automating a dropdown box requires automating each phase and locking the column to prevent editing, because anytime a user manually enters anything in the dropdown it will overwrite your formulas.

    When I implemented this with our team, I had our team enter an "Actual Start Date" and "Completion Date" and used those date fields as triggers for the "In Progress and Complete" Settings. The project would sit in Not Started until there was a date in the Actual Start column. That would trigger the In Progress. Then once a date was entered in the Completion Date it changed to Complete.

    I used the following Formulas based on my Columns...

    =IF(ISDATE([Completion Date]@row), "Done", IF(ISDATE([Actual Start]@row), "In Progress", IF(ISBLANK([Actual Start]@row), "Not Started")))

  • @Mike Wilday This is helpful - thanks!

Sign In or Register to comment.