Formula + conditional formatting help

[Karthik Thandapani]

Hi,

I know this can be easy for few here but I'm trying to find a solution to this problem. The attached sheet has a list of name with start and end time. All I need is to conditional format the cells below the times matching against the names.

Can anyone pls help/suggest.

Thanks,
Karthik

 

[Luke M]

CF formula in E16:
=ROUND(E$15,10)=ROUND(MEDIAN(E$15,INDEX($E$2:$F$12,MATCH($D16,$A$2:$A$12,0),)),10)

MEDIAN lets us check if a value is in between 2 others. The ROUND functions are because XL tends to get screwed up with times.

 

[Karthik Thandapani]

CF formula in E16:
=ROUND(E$15,10)=ROUND(MEDIAN(E$15,INDEX($E$2:$F$12,MATCH($D16,$A$2:$A$12,0),)),10)

MEDIAN lets us check if a value is in between 2 others. The ROUND functions are because XL tends to get screwed up with times.

Thanks Luke. You're a genuis.
Say if the names are randomly appearing, only few. Now we may need to look up for the value in the below table which is unchanged. How to do? Your answer will unlock the treasure ;-)

Please refer to the attachement

 

[Karthik Thandapani]

Hey Luke, there seems like a problem. If B is repeating with a different start & end time, that should color against it, correct? But it's not working here. Should we need to tweak a bit? Pls advise

 

[Luke M]

Regarding your first question, the formula would be the same even with fewer people. However, you moved the Time header labels up to row 8, so formula would need to change. It would be better to put the CF in, and then change the sheet in the future.
CF formula in E9:
=ROUND(E$8,10)=ROUND(MEDIAN(E$8,INDEX($E$2:$F$12,MATCH($D9,$A$2:$A$12,0),)),10)

For the second question, this CF method is limited to 1 time slot per person. If you're wanting multiple stop/starts I would suggest using one of Chandoo's Gantt examples and taking a different approach.
http://chandoo.org/wp/tag/gantt-charts/

 

[Karthik Thandapani]

thanks Luke. Much appreciated