Formula Counting Courses Required /Taken

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

Find more posts tagged with

Formulas

Project Management

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…