Group Scheduling Support - complicated scheduling

I'm unsure if Excel has this ability - currently, I'm manually building group schedules for my organization each week. There are 3 cohorts (e.g junior, intermediate, and senior), in differing weeks, with each week's groups that they would "typically" complete. These often move around within the week, that's no big deal. But, what is difficult is when a group gets cancelled. Without manually tracking that, it can get lost, which adds to mistakes in the schedule.

My biggest question is this - is there a way( through code or otherwise) that whatever group is entered in one of the sheets (e.g. Week 1, Week 2 etc) that this automatically is captured on a different sheet (e.g. like a Master List...). It would follow that cohort (the sheets are already set up to identify the cohort they are in). That way I can check groups missed to ensure that they are booked into subsequent weeks - ensuring no programming is missing.

I'm just not sure its possible and/or how to begin to think about this problem in a way that excel could support.

Any thoughts let me know. Any clarifying questions, fire away. Thanks in advance.