MULTIPLE CHECKBOX TO DETERMINE PROGRESS OF PROJECT AND AUTO UPDATING STATUS

Michelle Basson

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

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

Michelle Basson

Hi Mark.

Vlookup makes so much more sense.

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

Regards

MB