Prevent Formula Overriding


Hi there!

I have a column set up to indicate Task Progress (can choose from dropdown: Not Yet Started, In Progress, Nearing Completion, etc.) but also would like it to automatically mark "Complete" if another column (checkbox formatting) has a marked checkbox. 

The issue I'm having is that the formula erases if someone chooses a value from the dropdown/types in their own. This means that when the task is checked as complete in the checkbox column, the Task Progress column does not update to "Complete" and the user has to manually change it. 

To try to prevent this- is there any way to set it to where a formula is not overridden by manual input? Thank you! 


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Madison,

    Unfortunately, it's not possible at the moment to have a formula and also change values manually, but it's an excellent idea!

    Please submit an Enhancement Request when you have a moment.

    A possible workaround would be to have a formula that changes the status referencing the date and other cells.

    If you still want to be able to change the value manually, then you could use the third-party service, Zapier, to change the value to complete, and it's not dependent on any formula.

    Would one of these options work? Is Zapier an option for you?

    I hope that helps!

    Have a fantastic day!


    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD



    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Hi Madison, 

    As Andree stated, this isn't currently possible. If you want to automate the process you have to automate the entire process or not at all. Any manual adjustment of a field that has a formula in it, will overwrite the formula and negate it. To work around this our team automated the entire process. We created Actual Start and Actual Finish date columns and then used the following formula to indicate the status of our project. 

    =IF(AND(Isblank([Actual Start]@row), Isblank([Actual Finish]@row)), "Not Started", IF(ISBLANK([Actual Finish]@row), "In progress", IF(ISDATE([Actual FInish]@row), "Complete")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!