Populate Names against Dates and Course Names

[TerryE123]

Hi all
I have a tab that contains Trainers names and then dates along the top for the next 12 months.
I can then add relevant course names to each Trainer/Dates. (see first example in the image) the actual tab runs across for 52 weeks.

I have another tab where I can select a Trainer Name from a drop down and another to select the month and this is emailed to the Trainer each month.

But I also want to show is who else will be with them running that course? (See third example) I have tried making changes to the other formula but am getting #value error messages every time.

Can anyone advise me as to how I can populate the names as shown in the last example?
Thank you very much in advance

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[TerryE123]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

HI Mumps, I was unable to get the add in to work so have used drobox instead

Dropbox

www.dropbox.com

Tab called Schedule is a rolling 52 week "calendar" with Name of Trainers in Column A
Courses are assigned to one or more trainer if needed

The Monthly Summary tab allows us to select a name and a month from the drop down boxes at the top and this populates all the courses and activity for that trainer.
The tab called Monthly Summary Required is where I am stuck I want the tab to include the dates and the courses that trainer will be running (similar to the Monthly Summary tab)AND I want to include, under that date and course name any other trainer assigned to that same course. This last tab has an example of how it should look but the names added are static values as I cannot fathom out a way of doing this?
I have tried to switch around the formula I have used in the Monthly Summary tab but to no avail.
What I am asking for help with also needs some caveats
eg the names below it should not include the name of the person selected in the drop down box and it should not match any names for certain activities such as Annual Leave, Sick, Non Working Day etc
Hope this makes sense and someone can help me with .
Thanks to anyone who takes the time to read and hopefully respond
Regards
Terry