Excel conundrum

[fuddam]

Am doing an OS/App migration. I created a simple dashboard to give an overview.

The goal is to have a flag pop up on the scheduling sheet, based on the clash with staff holidays (red, green)

This pic shows the schedule, colour-coded, with the Availability column on the right (with dummy colour-coding to give you the intended result).



This shows the staff holidays/leave. Red columns are month totals, orange cells are specific dates away.
It has Grouping levels to make an overview easy.



As each department is migrated, there is an associated rollout date. If a staff member is away in the 7 days preceding the migration, I want it to flag up in the Availability column on the schedule.

Ideal would be to actually have the schedule show WHO is away during a project.

I've been playing around with all sorts of functions, incl IFs, date functions, vlookups, even array formulas, but can't crack it.

Any ideas?

[abaxas]

Years of rollout experience in the early 2000s told me.

Never try to organize anything, tell people when it's being done and stick to it. End of.

[fuddam]

Ah, I need to be more precise.

Those staff members are not BEING migrated, they are part of the migration team, which means their availability affects the ability to migrate a department. This is a pretty tricky process, since each department has unique applications on top of the base build etc. etc. Lots of tech headaches.