If cell matches another cell, apply conditional formatting to another cell

[SuperX7]

Hi everyone...

This one's stumped me. I'm trying to create a staff roster where employees are selected from a drop down list.

The issue is that there could be instances where the same employee (in this case Employee 1) is working in a different location so may be listed elsewhere.

Is it possible, to conditional format the start and finish times IF the employee is listed elsewhere AND if the times fall within the same time window to avoid "double booking" or incorrect scheduling?

For example below I've managed to conditional format

B58:
=AND(B58>B48,B58<C48) - 1200 is after 1000 and before 1800

C58:
=(C58<C48) - 1700 is before 1800

Which indicates the schedule isn't possible as the times fall within ROW 48 with the same employee

This may be a long shot but I have to ask the experts here as its driving me nuts!

 

[Eric W]

How about:

Book1
	A	B	C
1	Name	Start	End
2	Employee 1	10:00	18:00
3
4	Employee 2	8:00	10:00
5
6	Employee 3	8:00	10:00
7
8	Employee 3	12:00	14:00
9
10	Employee 3	9:30	11:00
11
12	Employee 1	12:00	17:00
13
14
15
16
17
18
19
20
Sheet10

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C20	Expression	=SUM(($A$2:$A$20=$A2)*(ROW($A$2:$A$20)<>ROW(A2))*(IF($C$2:$C$20<$C2,$C$2:$C$20,$C2)-IF($B$2:$B$20>$B2,$B$2:$B$20,$B2)>0))	text	NO