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

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)

image.png


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

image.png


Any feedback is much appreciated!

Best Answers

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!