What formula(s) would work best for this task?

BCK Team

Hello Smartsheet community!

I have a question regarding what formula(s) would work best for a specific spreadsheet I am building. The situation at hand:

I have a spreadsheet with data that has been submitted through a Smartsheet form. This data is connected to a users alias and tracks their completion of training. There are multiple different training courses that will make their completion of training as a whole 100%. Each training course requires a new form submission. Once they have completed each training course, they will take the post training survey (the form hosting all data) so anyone taking the survey has completed that course of training.

From this raw data spreadsheet (holding all training course completion data), I am wanting to create a different spreadsheet that looks at each users alias, each training course that has been completed and from there marks their entire training as completed based off of what responses have come form the form.

Example: Alias #1 has submitted 14 out of 14 training course survey responses, their training completion is marked as complete.

Example: Alias #2 has submitted 12 out of 14 training course survey responses, their training completion is not marked as complete.

I am thinking an IF, INDEX and MATCH formula may do the job but I am not confident with this theory.

Photos below of sheets:

Raw Data Spreadsheet: (Responses from form)

Spreadsheet that looks for training completion: (connected to raw data spreadsheet)

Any feedback is much appreciated!

Paul Newcome

Will the last one always be "Final Assembly"?

You can use the below if you need to specify a number of form entries:

=IF(COUNTIFS({Source Sheet Alias}, Alias@row) = 14, "Complete")

Paul Newcome

Here is how it would look for "Incomplete".

=IF(COUNTIFS({Source Sheet Alias}, Alias@row) = 14, "Complete", "Incomplete")

If you wanted to keep it all on one sheet (assuming you have the employee level on the same sheet), you can use an IF statement to automate the "14". It would work something like this...

To change the number:

IF([Employee Level]@row = "L1", 7, IF([Employee Level]@row = "L3", 14))

Then we would drop that in place of the 14 in the above like so...

=IF(COUNTIFS({Source Sheet Alias}, Alias@row) = number_formula, "Complete", "Incomplete")

=IF(COUNTIFS({Source Sheet Alias}, Alias@row) = IF([Employee Level]@row = "L1", 7, IF([Employee Level]@row = "L3", 14)), "Complete", "Incomplete")

Paul Newcome

Will the last one always be "Final Assembly"?

You can use the below if you need to specify a number of form entries:

=IF(COUNTIFS({Source Sheet Alias}, Alias@row) = 14, "Complete")

BCK Team

Depending on what level the employee is, it will dictate how many form entries they must complete.

Example: An L3 employee will complete 14 out of 14 but an L1 employee will complete 7 out of 14.

To work around this, I can create different sheets for each level of employee! This formula worked!! It is much more simpler than I expected it would be.

Is there a way if they have not yet submitted all of their forms it returns as "Incomplete"? Right now the cell is just left blank.

Photo:

Thanks again :)

Paul Newcome

Here is how it would look for "Incomplete".

=IF(COUNTIFS({Source Sheet Alias}, Alias@row) = 14, "Complete", "Incomplete")

If you wanted to keep it all on one sheet (assuming you have the employee level on the same sheet), you can use an IF statement to automate the "14". It would work something like this...

To change the number:

IF([Employee Level]@row = "L1", 7, IF([Employee Level]@row = "L3", 14))

Then we would drop that in place of the 14 in the above like so...

=IF(COUNTIFS({Source Sheet Alias}, Alias@row) = number_formula, "Complete", "Incomplete")

=IF(COUNTIFS({Source Sheet Alias}, Alias@row) = IF([Employee Level]@row = "L1", 7, IF([Employee Level]@row = "L3", 14)), "Complete", "Incomplete")

BCK Team

@Paul Newcome this is amazing and so helpful!! Thank you SO much :)

My sheet is so organized and streamlined now!

Paul Newcome

Happy to help. 👍️