Adding Data to a second schedule

Hi Guys,

I need help with this schedule I've created.

Firstly I am a total rookie with excel formulas

I have the Schedule organized as I would like it to but its the tracking that I'm having trouble with.

I have it designed in the way that is visible to my team

Whenever i want to make my reports I need to manually fish out my data. for example:

Sheet 1: The schedule Color coded for different staff (Senior teacher, Teacher, Teacher assistant) (each cell represents 15 min.)
Sheet 2: Same as sheet 1 but for the next week
Sheet 3: Shows a week by week schedule of a certain task (OPT) and which staff member is doing it. (All manually entered)
Sheet 4: Same as sheet 3 but showing other types of Scheduled tasks (LC's and Demos) ALSO ALL MANUALLY ENTERED


I have a million things i want to do with this schedule but the main things for now is

A) If i put OPT on (said week) for (said teacher) at (said time) then it will automatically enter it on the "OPT" sheet with that "said teacher's" name for the correct week, day and time.

B) Same as "A" but for the "activities" Sheet

Ive gotten the schedule times written out but I dont know how to make Excel understand search for the certain color blocks, analyze the size of block, check the day and time of the block and then have it transfer that and match the day, time and color but write the "Name" of the task and the Name of the teacher within the box.

Again I'm new to formulas so i have no clue how to go about it or even how to start

Can you guys help?

I've added the Schedule