Weekly Rotation Schedule

So I'm trying to setup a schedule and there's so much that needs to go on, I can't even figure out where to start.

Here's what I'm trying to do. The start of week is always Monday (regardless of holiday). Every Monday a new person is assigned to admin role 1/2/Super Admin. Admin 1 pulls names from Group1, while Admin 2 pulls from group 2. Super admin pulls from the super admin tab.

Now here's the catch. If Admin 1 is also in Super Admin, then they automatically become super admin for that week too. Otherwise the Super Admin group should rotate through. I really need this to be dynamic so the groups of people can constantly be update (add/remove) and still work properly without having to update code.


Also note worthy, I messed around with function to put the current week date and the following week date in Cells B1 and B7. This is something I'd like to keep and if there's a better way to pull the date, please let me know.