On Track/Off Track

Kasey Mabary · 02/26/25

I'm trying to find a way to let employees know if they are on or off track with a project based on the status and the boxes that are checked.

If the OEC Status is in an OEC - Kickoff Status and all of the boxes are checked in the yellow section, then the column returns a "Yes" value. I'm able to get this to work, but when I try and stack the statuses is where I have trouble.

All Statuses will start in an OEC - Kickoff Status and then move to another status, where additional boxes must be checked. For instance: if the OEC Status moves to "BLD - Build in Progress" then all of the previous yellow boxes must be checked and the light blue boxes must also be checked. If one of the boxes is not checked, then it should return a "No" Value. If all boxes are checked then it should return a "Yes" value.

I tried automations first and recieved an error for an infinate loop. I then tried calcualtions, but can't get them to work correctly. Anyone have any suggestions?

Brian Wilson DC · 02/27/25

Good morning, @Kasey Mabary. I think I was able to create what you are looking to do. Please let me know if this is not the direction you were looking to take your response.

In order to make this work you are going to need to introduce an OEC Status Helper Column and then one "Helper" column for each OEC Status grouping you have. So in your example above you shared "OEC - Kickoff" and "BLD - Build In Progress" as two phases. Each one of those will get a helper column to calculate the value of your checkboxes. This helper column, once programmed, can be hidden from view.

Below is the spreadsheet I made with your data. You will see that the formula automatically assigns "OEC - Kickoff" to the OEC Status column if the total number of checkboxes is 5 or less. This would be the equivalent of checking off every box under the Kickoff options. Then it automatically assigns a value of "BLD - Build In Progress" to the OEC Status if the check boxes total 6 thru 10. Which would be the boxes correlating to the next phase, plus having completed all of the kickoff phase previously. It then assigns a value of "No" when the check box total is LESS than 5 and status is "OEC - Kickoff", or when the total is less than 10 and the status is "BLD - Build In Progress." It assigns a value of "Yes" when the OEC Status is "OEC - Kickoff" and the total number of check boxes is 5, etc.

For the "On/Off Track" Column it is a Text/Number column with the following column formula:

=IF(AND([OEC Status]@row = "OEC - Kickoff", [Kickoff Helper Column]@row = 5), "Yes", IF(AND([OEC Status]@row = "BLD - Build In Progress", [Before Build Helper Column]@row = 5), "Yes", "No"))

For the OEC Status column you will need to replace your dropdown with this column formula in a Text/Number column:

=IF([OEC Status Helper Column]@row <= 5, "OEC - Kickoff", IF([OEC Status Helper Column]@row <= 10, "BLD - Build In Progress"))

For the OECE Status Helper Column (which you can hide) use the following column formula, which you will add more helper columns to for the other categories you have:

=[Kickoff Helper Column]@row + [Before Build Helper Column]@row

For the helper columns for Kickoff Helper and Before Build you will use these formulas, and again, need to add the same type of formula for any other categories you need a checkbox section for:

=COUNTIFS([Kickoff - SP Status Updated]@row:[Verify OE Wizard is Disabled]@row, 1)

=COUNTIFS([Before Build - SP OE Type Updated]@row:[Before Build - SP Docs Received Task]@row, 1)

I hope this helps! Please let me know if you this is not what you were looking for, or if it is and you need help expanding the formulas.

Regards,

Brian

Brian Wilson DC · 02/27/25

Good morning, @Kasey Mabary. I think I was able to create what you are looking to do. Please let me know if this is not the direction you were looking to take your response.

In order to make this work you are going to need to introduce an OEC Status Helper Column and then one "Helper" column for each OEC Status grouping you have. So in your example above you shared "OEC - Kickoff" and "BLD - Build In Progress" as two phases. Each one of those will get a helper column to calculate the value of your checkboxes. This helper column, once programmed, can be hidden from view.

Below is the spreadsheet I made with your data. You will see that the formula automatically assigns "OEC - Kickoff" to the OEC Status column if the total number of checkboxes is 5 or less. This would be the equivalent of checking off every box under the Kickoff options. Then it automatically assigns a value of "BLD - Build In Progress" to the OEC Status if the check boxes total 6 thru 10. Which would be the boxes correlating to the next phase, plus having completed all of the kickoff phase previously. It then assigns a value of "No" when the check box total is LESS than 5 and status is "OEC - Kickoff", or when the total is less than 10 and the status is "BLD - Build In Progress." It assigns a value of "Yes" when the OEC Status is "OEC - Kickoff" and the total number of check boxes is 5, etc.