Tricky dependent drop-down list.

Hi. Would appreciate for help or for hints.

I am making a vacation chart for our small company. There are 6 employees in the company:
John, Michael, Ben, Alice, Ad and Sandra. We have several rules in our company concerning who will make college’s duty when he is on vacation.

Here are the rules:
If John is on vacation, then only Michael and Ben can be on vacation at the same time (Alice, Sandra and Ad will make work for John)
If Michael is on vacation, then only Ben, Alice, Sandra and John can be on vacation at the same time (Ad will make work for Michael.
If Ben is on vacation, then only Alice and Ad can be on vacation at the same time (John, Michael and Sandra will work for Ben.
I hope you got my point.
What I’ve done so far is to make a table for one year on per week vacation basis
Priority is from top to bottom. Person who askes for vacation first on some particular week have a priority. He put his name on top of that week. If another co-worker wants to have a vacation on the same week, then previously described rules are applied, and so on.
The first row is very easy, since its just drop-down list of all our workers and everyone could put their name in there.
The second row is a bit trickier, since it will depend on who is on top of the list. I’ve managed to do that with dependent drop-down list with data validation
The problem appeared on third and forth rows (if three or four people wish to have a vacation at the same time)
I can’t figure out how to make third drop down-list be dependant simultaneously on first and second row.

For example. John takes vacation on week 29
If you press second row, then you can only choose Michael and Ben
Suppose you choose Michael.
Then if you press third raw (that’s what I want from excel) It should understand that Both John and Michael are on vacation, which means:
1) There can’t be John and Michael names in third drop-down list since they are already used.
2) There can’t be Alice, Sandra and Ad (they will do the work for John)
3) There can’t be Ad (he will do the work for Michael)
4) There should be only Ben left to choose

Help is very appreciated!

Welcome to the forum.

A picture of your workbook is of little use - please attach the real thing. Instructions at the top of the page.

The file you attached has different names than in your description. It makes it difficult to follow your logic.