Formula Counting Courses Required /Taken

Art Miller

All,

Have a smartsheet that shows by student (each row is a student) what course they are required to take.  The student updates the date they completed the course via update requests.   Based on this smartsheet I am trying to find a formula that will show me when they have completed all courses that they are required.   The required course columns are a dropdown with a yes if the need to take the course and blank if they do not.   The completion columns are date columns that they update if they have completed the course, and blank if they had not.  My thought was to create a formula that would produce a one if the student had a yes in any of the course fields, and a date in any of the completion cells.  This would tell us that they had completed all the courses they are required to take. 

I have tried unsuccesfully to create anything that will work.  Appreciate any suggestions on how we accomplish this.

 

Thanks!

Art

 

Chris McKay

Hi Art,

Perhaps a solution like the below might do what you need.

As per the first image, each course will have a Course # Complete checkbox column alongside the Course # dropdown and Course # Date columns. The Course # Complete column formula would be:

=IF(AND([Course 1]1 = "Yes", NOT(ISBLANK([Course 1 Date]1))), 1, IF([Course 1]1 = "No", 1, 0))

This will mark the course complete if the Course # dropdown is set to No, or set to Yes with a corresponding date value.

The Requisites Complete checkbox column will have the following formula:

=IF(AND([Course 1 Complete]1 = 1, [Course 2 Complete]1 = 1, [Course 3 Complete]1 = 1), 1, 0)

As per the second image, you can then choose to set conditional formatting to look for a check in the Requisites Complete column and hide the Course # Complete columns to keep things looking neat.

This is only one of many solutions you could use to tackle this problem but should get you started.

Art Miller

Thanks Chris!  I did what you suggested and I think it will work.

Chris McKay

My pleasure Art!