Override for Formula Column

I have a formula column that is updating a status based on an end date column. I have to keep it a formula column because the information in the sheet will be coming from form entries so I want to ensure that every new row has the status determined by the formula. That said, I need to create an override to manually apply the "archive" status. I wanted to do this with a checkbox. I am trying to write this into the formula in the Status column but I'm getting an unparseable error. I'm sure there's probably an error in my formula:

=IF(Category@row = "Purchase", "No Date", IF(OR([Archive Override]@row#=1),([End Date]@row < DATE(2022, 9, 1)), "Archive", IF([End Date]@row < TODAY(), "Expired", IF([End Date]@row - TODAY() <= 90, "Notifying", "Current")))))

Formula aside, is this the best way to accomplish what I'm trying to do?


Best Answer

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Answer ✓

    Hi @BB2791 - I think I see what you're saying. Please let me know if this works for you.

    =IF(Category@row = "Purchase", "No Date", IF(OR([Archive Override]@row=1, [End Date]@row < DATE(2022, 9, 1)), "Archive", IF([End Date]@row < TODAY(), "Expired", IF([End Date]@row - TODAY() <= 90, "Notifying", "Current"))))

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

Answers

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Answer ✓

    Hi @BB2791 - I think I see what you're saying. Please let me know if this works for you.

    =IF(Category@row = "Purchase", "No Date", IF(OR([Archive Override]@row=1, [End Date]@row < DATE(2022, 9, 1)), "Archive", IF([End Date]@row < TODAY(), "Expired", IF([End Date]@row - TODAY() <= 90, "Notifying", "Current"))))

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • BB2791
    BB2791 ✭✭✭✭

    Hi @Amber Eakin. Yes, that worked. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!