I've been trying to digest your requirements. It's a lot to take in. My feeling is that you'll need another tab, for the students. This would list all the potential students, and the courses they want to take. There should be some mechanism to keep them from taking 2 classes that are scheduled on the same days. This would also allow some routine somewhere to decide how many people are in a course.
Your course table should probably have a column for how many people can be in the course, and how many instructors are needed (this might just be a formula).
Your Trainer table should probably have a place for courses that each trainer is teaching.
I can also say pretty confidently that this will require at least one macro, probably more. There might be some formulas and Conditional Formatting, but the heavy lifting would have to be done in a macro. It's just too complicated. I should also warn you that this kind of scheduling problem is HARD. It takes more than just knowledge of VBA, you have to come up with an algorithm to figure out where to put everything that doesn't violate any of your requirements. Things like: there are 2 potential trainers for course A, but only one of them can teach course B. So make sure to assign course B first, or if you pick the wrong one for course A, you won't have anyone for course B.
I used to attempt macros like this, but not for a long time. Two possible (but bad) approaches were to exhaustively calculate every combination, throw out the ones that violate some rule, and keep the best; or to randomly assign people to teach, and if it didn't work, try again. Give up if you can't find a working solution in 1000 tries.
I'm going to bow out, since I don't have enough time to devote to this right now. But I have 2 suggestions. First, if you break this up into small enough pieces, you might be able to get people here to do a bit at a time. Part 1 - get from your course tab to the schedule tab with a macro. Also mention if you always will start from scratch, or if you will be updating the course tab, and want to keep the items on the existing schedule. Part 2 - How to update the schedule with the info from your student tab. Have an "Enrolled" column. Maybe changing the formatting colors. Part 3 - from the Trainer tab, the Annual Leave tab, Student, and the Schedule tab, create a way to create drop down lists for Trainers for each of the unassigned courses. This would need to be redone every time something on any of those tabs change. Part 4 - create a macro that will automatically assign all the trainers. That'll be tricky.
Second option, click on the MrExcel Publishing tab on the top and select Excel Consulting Services. These are paid consultants, but they are all vetted carefully and are very good options. You'll likely get a more cohesive solution that way.
Either way, check out the xl2bb add-in tool (see the link in the reply box or my signature). It makes it much easier to display your spreadsheet, and people are more inclined to help than with just a picture.
Sorry this is so long! But I just wanted to give you the best options I know of. Good luck!
