Using COUNTIF (?) to list missing training in scheduling spreadsheet

Hi all,

attached is a sample spreadsheet I am building to help me schedule training sessions for team members across different teams in a large organisation. You will see the training sessions scheduled in columns M and P to give you an example of how I would mark each training session.

What I am looking for is a formula for column E, which (for each team member) compares the range F through to NF to a list of predefined training session names (see sheet 2) and lists the missing values in column E. So for example, for all three team members in the sample, the formula should pick up that there is no training scheduled for "mandate", and it should display this in column E. If multiple training sessions are missing, it would be great to have them comma-separated.

I'm open to any other suggestions, as I am a novice excel user and unsure if countif is the most appropriate solution. A bit of background in case it matters - I am considering using a table format for each of the departments, to allow me to sort the newstarters by start date or manager, as sometimes only a particular team may require training, but generally it makes sense to sort by start date. The hiring in this organisation is very staggered, which is why I use the coloured cells in F-NF to mark when a team member started and where they are at in their onboarding (green for month 1, yellow for month 2 and so on).

Previously, I did it the other way around, with a concatenate formula in column E showing me all training the team member had been scheduled for/attended. However, this became quite messy and it meant I had to manually compare column E to the list of available training for each team member.

Looking forward to any and all suggestions, thank you in advance!

Please try in E4 and copy down:

Formula:

Please Login or Register  to view this content.

You are Welcome!

Thanks for the feedback and glad to have helped .

Also, if you have not already done so,
you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Thanks for the feedback and rep. . Glad to have helped.

Is in this case creating a seperate table an acceptable solution?

In that case plese try:

Please Login or Register  to view this content.

See range A26:E36.

You are Welcome!

Yes it is easy to change the colour of a person first month in green:

In your example is H4-AN4 in green (excluded Saturday and Sunday).
This is a little bit more as a month (33 days),

This CF-rules turns the cells the first 33 days green except Saturday and Sunday:
Please try CF-rule applies to $F$4:$NF$25

Formula:

Please Login or Register  to view this content.

You should also add real dates in the headers.
Please empty all dates in F1:NF1 and try in F1: =SEQUENCE(,365,DATE(2024,1,2)) and format cells F1:NF1 as ddd.

First month: Please try CF-rule:

Formula:

Please Login or Register  to view this content.

Second month: Please try CF-rule:

Formula:

Please Login or Register  to view this content.

and so on

You are Welcome!

Thanks for the nice feedback. Glad to have helped .

This part of the Excel forum is a free forum, where every member can provide solutions voluntarily and free of charge.
You can thank the members by adding reputation by clicking on * Add reputation left below their answers.

Next time, there is also an option to purchase points on Commercial Services, https://www.excelforum.com/payments.php after which you can post a question for which you want to pay.
These questions can only be answered by administrators, moderators and gurus.
If you have a preference for someone to answer your question, you can indicate this.