Help with a dynamic status column

Options

Hi-

I have a {{Status}} which is a drop down containing the follow options "Not Started", "In Process", and "Complete". I have 4 columns which are checkboxes. I would like the {{status}} to dynamically update based on the check boxes in the columns.

If all the checkboxes@row = 0 then status would be "not started" but if any of the checkboxes = 1 then the status would be "In Process". When all the checkboxes = 1 I'm hoping the status can return a "Complete" status.

Best Answer

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

    Hello, @JIM_AFO ! I believe I have a formula that will work for you. Please let me know if it helps!

    =IF(AND([Gather Bank & Investment Statements]@row = 1, [Complete Accounting & Update Workpapers]@row = 1, [Review of accounting]@row = 1, [Ensure checklist is completed]@row = 1), "Complete", IF(AND([Gather Bank & Investment Statements]@row = 0, [Complete Accounting & Update Workpapers]@row = 0, [Review of accounting]@row = 0, [Ensure checklist is completed]@row = 0), "Not Started", IF(OR([Gather Bank & Investment Statements]@row = 1, [Complete Accounting & Update Workpapers]@row = 1, [Review of accounting]@row = 1, [Ensure checklist is completed]@row = 1), "In Process")))

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

Answers

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

    Hello, @JIM_AFO ! I believe I have a formula that will work for you. Please let me know if it helps!

    =IF(AND([Gather Bank & Investment Statements]@row = 1, [Complete Accounting & Update Workpapers]@row = 1, [Review of accounting]@row = 1, [Ensure checklist is completed]@row = 1), "Complete", IF(AND([Gather Bank & Investment Statements]@row = 0, [Complete Accounting & Update Workpapers]@row = 0, [Review of accounting]@row = 0, [Ensure checklist is completed]@row = 0), "Not Started", IF(OR([Gather Bank & Investment Statements]@row = 1, [Complete Accounting & Update Workpapers]@row = 1, [Review of accounting]@row = 1, [Ensure checklist is completed]@row = 1), "In Process")))

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!