We should have started there as, if doing that, the conditional formatting becomes extremely simple - see below.
Formula in B5 is copied across and down.
Remove the old CF and apply this one.
Tables (4)
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U |
4 | | 16-Jan-19 | 17-Jan-19 | 18-Jan-19 | 19-Jan-19 | 20-Jan-19 | 21-Jan-19 | 22-Jan-19 | 23-Jan-19 | 24-Jan-19 | 25-Jan-19 | 26-Jan-19 | 27-Jan-19 | 28-Jan-19 | 29-Jan-19 | 30-Jan-19 | 31-Jan-19 | 01-Feb-19 | 02-Feb-19 | 03-Feb-19 | 04-Feb-19 |
5 | TEAM1 | A | A | A | A | A | A | A | A | | | | D | D | | | | | | | |
6 | TEAM2 | | | | | | | | B | B | B | B | B | | | | | | | | |
7 | TEAM3 | | | | C | C | C | C | C | C | C | C | C | C | C | C | C | C | C | C | C |
8 | TEAM4 | | E | E | E | | F | | | | | | | | | G | G | G | G | G | |
9 | | | | | | | | | | | | | | | | | | | | | |
10 | | | | | | | | | | | | | | | | | | | | | |
11 | | | | | | | | | | | | | | | | | | | | | |
12 | | | | | | | | | | | | | | | | | | | | | |
13 | | | | | | | | | | | | | | | | | | | | | |
17 | | | | | | | | | | | | | | | | | | | | | |
18 | | | | | | | | | | | | | | | | | | | | | |
19 | Team Name | START DATE | | | | | END DATE | | | | | | | | | | | | | | |
20 | TEAM1 | 16-Jan | | | | | 23-Jan | | | | | A | | | | | | | | | |
21 | TEAM2 | 23-Jan | | | | | 27-Jan | | | | | B | | | | | | | | | |
22 | TEAM3 | 19-Jan | | | | | 10-Feb | | | | | C | | | | | | | | | |
23 | TEAM1 | 27-Jan | | | | | 28-Jan | | | | | D | | | | | | | | | |
24 | TEAM4 | 17-Jan | | | | | 19-Jan | | | | | E | | | | | | | | | |
25 | TEAM4 | 21-Jan | | | | | 21-Jan | | | | | F | | | | | | | | | |
26 | TEAM4 | 30-Jan | | | | | 3-Feb | | | | | G | | | | | | | | | |
27 | | | | | | | | | | | | | | | | | | | | | |
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:89px;"><col style="width:99px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:84px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:75px;"><col style="width:77px;"><col style="width:77px;"><col style="width:77px;"><col style="width:77px;"></colgroup><tbody>
</tbody>
Spreadsheet Formulas |
Cell | Formula | B5 | =IFERROR(INDEX($L$20:$L$33,AGGREGATE(15,6,(ROW($L$20:$L$33)-ROW($L$20)+1)/(($A$20:$A$33=$A5)*($B$20:$B$33<=B$4)*($G$20:$G$33>=B$4)),1)),"") |
<tbody>
</tbody> |
<tbody>
</tbody>
Conditional formatting |
Cell | Nr.: / Condition | Format | B5 | 1. / Formula is =B5<>"" | Abc |
<tbody>
</tbody> |
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4