How to show a "Completion Status" column based off of certain cells containing value?

Ruby Wood

Hello Smartsheet community,

I have a database containing the amount of training specific employees have received. I would like to create a formula that is able to recognize what level someone is at and then the training needed for that level. If they have received the training it would show as "complete." If they have not received all of their training, it would show as "incomplete." Pictures below for detail:

This is the current sheet and how it looks:

I would like the "Completion Status" column to show that employee "Test 1" and "Test 3" are complete since they have received all trainings needed for the L1 level. I would like employees "Test 2" ", "Test 4" and "Test 5" to show as "incomplete" since they have not received all of their trainings.

Depending on the employee's level, the amount of training they receiving will be different. An L1 employee will receive 4 trainings to be fully complete, and L3 employee will receive 6 trainings to be fully complete.

Please let me know if you have any ideas on how to create this.

Thanks!

Ruby

Gulshan Sharma

Hi @Ruby Wood

We need to determine the employee level using If formula and then count "Trained" in the row as follows:

=IF(level@row="L1",if(countif(Dish@row:[HC Prep]@row,"Trained")=4,"Complete","Incomplete"), IF(level@row="L3",if(countif(Dish@row:[HC Prep]@row,"Trained")=3,"Complete","Incomplete"),)

However, this above formula will not validate the test name and may lead to wrong status is a wrong test is updated as trained and have to be careful while updating the test result.

Regards,

Gulshan

Bassam Khalil

Ruby Wood

Hello,

I did this and now the outcome of this formula shows a 0 only for the employees and L3 and L3+ status.

Here is the formula used:

=IF(Level@row = "L1", IF(COUNTIF(Dish@row:[LC Lead]@row, "Trained") = 6, "Complete", "Incomplete")) + IF(Level@row = "L1+", IF(COUNTIF(Dish@row:[LC Lead]@row, "Trained") = 10, "Complete", "Incomplete")) + IF(Level@row = "L3", IF(COUNTIF(Dish@row:[LC Lead]@row, "Trained") = 14, "Complete", "Incomplete")) + IF(Level@row = "L3+", IF(COUNTIF(Dish@row:[LC Lead]@row, "Trained") = 17, "Complete", "Incomplete")).

Do you have any idea why it would be this way?

Thanks.