How to create a multy rule based selection (conditional formatting)

[Klemzy]

Hey
I would like to create an automated excel spreadsheet for our sport event. We have many different teams form different countries and some game referees also from different countries.

The problem is: how to not do everything manually? How to make this process of arrangment atumated, so every time I change the teams I get a valid result for the referees.

In column B you have national referees listed.
I want to arrange referees into the column F. One referee per each game. It's important that all three rules are met, otherwise the result is not valid.
It's not a problem if some referees won't be arranged.

The RULES are:

1. Game referee can't be the same nationality as the both teams are.

2. Same game referee can't officiate two games at the same time.

3. Same game referee can't officiate more than two games after each other.

Sports schedule.xlsx
	A	B	C	D	E	F	G
1				GAME 1		NATIONAL REFEREE
2	TEAMS	REFEREES		SLO	AUT		<- Valid results could be: ITA 1, ITA 2, GER 1, GER 2, GBR 1, GBR 2
3	SLO	ITA 1
4	AUT	ITA 2		GAME 2
5	GER	GER 1		GER	FRA		<- Valid results could be: ITA 1, ITA 2, SLO 1, SLO 2, GBR 1, GBR 2
6	FRA	GER 2
7	SWE	SLO 1		GAME 3
8	ITA	SLO 2		SWE	ITA		<- Valid results could be: SLO 1, SLO 2, GER 1, GER 2, GBR 1, GBR 2
9	GBR	GBR 1
10		GBR 2		GAME 4
11				GBR	SLO		<- Valid results could be: ITA 1, ITA 2, GER 1, GER 2
12
13				GAME 5
14				FRA	AUT		<- Valid results could be: ITA 1, ITA 2, GER 1, GER 2, GBR 1, GBR 2, SLO 1, SLO 2
Sheet1

Cell Formulas
Range	Formula
D2	D2	=A3
E2	E2	=A4
D5	D5	=A5
E5	E5	=A6
D8	D8	=A7
E8	E8	=A8

Thanks for all your support and help

Klemzy

Also asked here Multi rule based selection

 

[Dan_W]

Thank you for including the cross-post information. I see that you have a solution.