MULTIPLE CHECKBOX TO DETERMINE PROGRESS OF PROJECT AND AUTO UPDATING STATUS

Good day,

I have multiple checkboxes which determines the current status of the project.

I want a column in the front to give me the details of the most recent checkbox in the row.

The checkboxes follow a specific order as the project progresses. So the first one must be complete before the second one can be selected.

I want the status column to update with the latest checkbox status automatically.

The standard if statement works when I select the first column, however I reckon I need nested if statements to make this work, but I keep getting #unparseable

=IF([FIRST AID]@row = 1; "FIRST AID")

my nested if statements are as follow:

=IF(AND([FIRST AID]@row = 1; "FIRST AID"; IF([FIRE FIGHTING]@row = 1; "FIRE FIGHTING")))

I know I am missing something.

Assistance will be greatly appreciated.


Regards

MB

Michelle Basson

Smartsheet Overachiever Alumni | Solution Consultant | Lover of everything Smartsheet

https://www.linkedin.com/in/michelle-basson/

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @Michelle B ,

    I think you can use MATCH because progress has to be sequential. Look for the first unchecked box using =MATCH(false,[First Aid]@row:[Contract Admin]@row, 0). No checkboxes will return = 1. A check in the 1st column = 2. Your formula would be:

    =IF(MATCH(false,[First Aid]@row:[Contract Admin]@row, 0),1, "Not Started", IF(MATCH(false,[First Aid]@row:[Contract Admin]@row, 0),2,"First Aid", IF(MATCH(false,[First Aid]@row:[Contract Admin]@row, 0),3,"Small Plant", IF.......

    Rather than imbedding all of the text into a formula you should consider setting up a 2 column table that lists the Match # and the status. Then use vlookup to find the status associated with the Match # and return the status. The table could be on your same sheet or set up as a separate sheet with a cross sheet reference. Using a table makes it easier to make changes in the future.

    That formula would look like:

    =vlookup(MATCH(false,[First Aid]@row:[Contract Admin]@row, 0), RANGE:RANGE, 2) Where RANGE:RANGE is the lookup table.

    Make sense?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Michelle Basson
    Michelle Basson Overachievers Alumni

    Hi Mark.


    Vlookup makes so much more sense.


    Thanks so much. Will try vlookup and post should I have any difficulty.


    Regards

    MB

    Michelle Basson

    Smartsheet Overachiever Alumni | Solution Consultant | Lover of everything Smartsheet

    https://www.linkedin.com/in/michelle-basson/

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!