If lookup value between 2 dates (Leave and Resource Schedule)

J

jdawg_1989

New Member
Joined
Jun 24, 2011
Messages
27
Hi All,

I have source data which comes in the following format:

Code: 
A                     B                  C                        D                        E
1  Name         Leave From    Leave To     TimePeriod From   TimePeriod To
2  Person 1      21/06/11       23/06/11             am                   pm
3  Person 1      16/06/11       16/06/11             pm                   pm
4  Person 1      20/06/11       21/06/11             am                   pm
5  Person 2      16/06/11       28/06/11             am                   pm
6  Person 3      17/06/11       18/06/11             am                   pm
7  Person 3      21/06/11       23/06/11             am                   pm
I have a resource tracker for the week, and want to lookup a person's name and want to return "1" if they are not on leave, a "0" if they are on leave and "0.5" for half a day's leave.

I've tried for days now and cannot get a forumula to get close! I've tried SUMPRODUCT, Index and so on with no result.

Can you help me please?
 
Sort by date Sort by votes
I don't think I have enough posts to be able to attach the file yet, I don't have the option:(

When I am able to upload, I've stripped some of the data out due to privacy. But I have kept 6 examples in with alias's.
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
The following shows my resource tracker with the sumproduct forumla in cells N6:AB11, this is quite processor hungry especially as I have ~30 people on my list.

Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th><th>AF</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="font-style: italic;color: #7030A0;;">Resource Tracker</td><td style="font-style: italic;color: #7030A0;;">
</td><td style="font-style: italic;color: #7030A0;;">
</td><td style="font-style: italic;color: #7030A0;;">
</td><td style="font-style: italic;color: #7030A0;;">
</td><td style="font-style: italic;color: #7030A0;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: right;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BFBFBF;;">Week Commencing:</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #FF0000;;">20/06/2011</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Week Commencing: 20-Jun-11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Week Commencing: 27-Jun-11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Week Commencing: 04-Jul-11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">
</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #BFBFBF;;">days available for this week:</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #BFBFBF;;">
</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #BFBFBF;;">
</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #FF0000;;">28</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">Name</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">Employee#</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">Manager</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">Office Location</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">Business</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">Job Type</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">Primary Role</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">Secondary Role</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">Third Role</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">Skill Set</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">Pri %</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">Sec%</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">Third %</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Mon</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Tue</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Wed</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Thu</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Fri</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Mon</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Tue</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Wed</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Thu</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Fri</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Mon</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Tue</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Wed</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Thu</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Fri</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">Comments</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">% Availability For Week</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">% Availability For pri</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">No Avail Days</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">25</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">75</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">100</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">100</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">100</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">100</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">100</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">100</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">100</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">100</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">100</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">100</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">80</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">4</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Person 5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">50</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">50</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">100</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">100</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">5</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">Person 6</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">100</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;;">100</td><td style="text-align: center;border-top: 1px solid black;;">80</td><td style="text-align: center;border-top: 1px solid black;;">4</td></tr></tbody></table>
Resource Tracker​


<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">N6</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A6),--(eLeave!$E:$E<=N$5),--(eLeave!$F:$F>=N$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">N7</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A7),--(eLeave!$E:$E<=N$5),--(eLeave!$F:$F>=N$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">N8</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A8),--(eLeave!$E:$E<=N$5),--(eLeave!$F:$F>=N$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">N9</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A9),--(eLeave!$E:$E<=N$5),--(eLeave!$F:$F>=N$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">N10</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A10),--(eLeave!$E:$E<=N$5),--(eLeave!$F:$F>=N$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">N11</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A11),--(eLeave!$E:$E<=N$5),--(eLeave!$F:$F>=N$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">O6</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A6),--(eLeave!$E:$E<=O$5),--(eLeave!$F:$F>=O$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">P6</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A6),--(eLeave!$E:$E<=P$5),--(eLeave!$F:$F>=P$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Q6</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A6),--(eLeave!$E:$E<=Q$5),--(eLeave!$F:$F>=Q$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">R6</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A6),--(eLeave!$E:$E<=R$5),--(eLeave!$F:$F>=R$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">S6</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A6),--(eLeave!$E:$E<=S$5),--(eLeave!$F:$F>=S$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">T6</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A6),--(eLeave!$E:$E<=T$5),--(eLeave!$F:$F>=T$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">U6</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A6),--(eLeave!$E:$E<=U$5),--(eLeave!$F:$F>=U$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">V6</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A6),--(eLeave!$E:$E<=V$5),--(eLeave!$F:$F>=V$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">W6</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A6),--(eLeave!$E:$E<=W$5),--(eLeave!$F:$F>=W$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">X6</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A6),--(eLeave!$E:$E<=X$5),--(eLeave!$F:$F>=X$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Y6</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A6),--(eLeave!$E:$E<=Y$5),--(eLeave!$F:$F>=Y$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Z6</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A6),--(eLeave!$E:$E<=Z$5),--(eLeave!$F:$F>=Z$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">AA6</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A6),--(eLeave!$E:$E<=AA$5),--(eLeave!$F:$F>=AA$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">AB6</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A6),--(eLeave!$E:$E<=AB$5),--(eLeave!$F:$F>=AB$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">O7</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A7),--(eLeave!$E:$E<=O$5),--(eLeave!$F:$F>=O$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">P7</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A7),--(eLeave!$E:$E<=P$5),--(eLeave!$F:$F>=P$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Q7</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A7),--(eLeave!$E:$E<=Q$5),--(eLeave!$F:$F>=Q$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">R7</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A7),--(eLeave!$E:$E<=R$5),--(eLeave!$F:$F>=R$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">S7</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A7),--(eLeave!$E:$E<=S$5),--(eLeave!$F:$F>=S$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">T7</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A7),--(eLeave!$E:$E<=T$5),--(eLeave!$F:$F>=T$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">U7</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A7),--(eLeave!$E:$E<=U$5),--(eLeave!$F:$F>=U$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">V7</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A7),--(eLeave!$E:$E<=V$5),--(eLeave!$F:$F>=V$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">W7</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A7),--(eLeave!$E:$E<=W$5),--(eLeave!$F:$F>=W$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">X7</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A7),--(eLeave!$E:$E<=X$5),--(eLeave!$F:$F>=X$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Y7</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A7),--(eLeave!$E:$E<=Y$5),--(eLeave!$F:$F>=Y$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Z7</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A7),--(eLeave!$E:$E<=Z$5),--(eLeave!$F:$F>=Z$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">AA7</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A7),--(eLeave!$E:$E<=AA$5),--(eLeave!$F:$F>=AA$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">AB7</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A7),--(eLeave!$E:$E<=AB$5),--(eLeave!$F:$F>=AB$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">O8</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A8),--(eLeave!$E:$E<=O$5),--(eLeave!$F:$F>=O$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">P8</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A8),--(eLeave!$E:$E<=P$5),--(eLeave!$F:$F>=P$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Q8</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A8),--(eLeave!$E:$E<=Q$5),--(eLeave!$F:$F>=Q$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">R8</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A8),--(eLeave!$E:$E<=R$5),--(eLeave!$F:$F>=R$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">S8</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A8),--(eLeave!$E:$E<=S$5),--(eLeave!$F:$F>=S$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">T8</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A8),--(eLeave!$E:$E<=T$5),--(eLeave!$F:$F>=T$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">U8</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A8),--(eLeave!$E:$E<=U$5),--(eLeave!$F:$F>=U$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">V8</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A8),--(eLeave!$E:$E<=V$5),--(eLeave!$F:$F>=V$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">W8</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A8),--(eLeave!$E:$E<=W$5),--(eLeave!$F:$F>=W$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">X8</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A8),--(eLeave!$E:$E<=X$5),--(eLeave!$F:$F>=X$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Y8</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A8),--(eLeave!$E:$E<=Y$5),--(eLeave!$F:$F>=Y$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Z8</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A8),--(eLeave!$E:$E<=Z$5),--(eLeave!$F:$F>=Z$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">AA8</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A8),--(eLeave!$E:$E<=AA$5),--(eLeave!$F:$F>=AA$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">AB8</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A8),--(eLeave!$E:$E<=AB$5),--(eLeave!$F:$F>=AB$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">O9</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A9),--(eLeave!$E:$E<=O$5),--(eLeave!$F:$F>=O$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">P9</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A9),--(eLeave!$E:$E<=P$5),--(eLeave!$F:$F>=P$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Q9</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A9),--(eLeave!$E:$E<=Q$5),--(eLeave!$F:$F>=Q$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">R9</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A9),--(eLeave!$E:$E<=R$5),--(eLeave!$F:$F>=R$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">S9</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A9),--(eLeave!$E:$E<=S$5),--(eLeave!$F:$F>=S$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">T9</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A9),--(eLeave!$E:$E<=T$5),--(eLeave!$F:$F>=T$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">U9</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A9),--(eLeave!$E:$E<=U$5),--(eLeave!$F:$F>=U$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">V9</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A9),--(eLeave!$E:$E<=V$5),--(eLeave!$F:$F>=V$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">W9</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A9),--(eLeave!$E:$E<=W$5),--(eLeave!$F:$F>=W$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">X9</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A9),--(eLeave!$E:$E<=X$5),--(eLeave!$F:$F>=X$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Y9</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A9),--(eLeave!$E:$E<=Y$5),--(eLeave!$F:$F>=Y$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Z9</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A9),--(eLeave!$E:$E<=Z$5),--(eLeave!$F:$F>=Z$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">AA9</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A9),--(eLeave!$E:$E<=AA$5),--(eLeave!$F:$F>=AA$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">AB9</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A9),--(eLeave!$E:$E<=AB$5),--(eLeave!$F:$F>=AB$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">O10</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A10),--(eLeave!$E:$E<=O$5),--(eLeave!$F:$F>=O$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">P10</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A10),--(eLeave!$E:$E<=P$5),--(eLeave!$F:$F>=P$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Q10</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A10),--(eLeave!$E:$E<=Q$5),--(eLeave!$F:$F>=Q$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">R10</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A10),--(eLeave!$E:$E<=R$5),--(eLeave!$F:$F>=R$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">S10</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A10),--(eLeave!$E:$E<=S$5),--(eLeave!$F:$F>=S$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">T10</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A10),--(eLeave!$E:$E<=T$5),--(eLeave!$F:$F>=T$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">U10</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A10),--(eLeave!$E:$E<=U$5),--(eLeave!$F:$F>=U$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">V10</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A10),--(eLeave!$E:$E<=V$5),--(eLeave!$F:$F>=V$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">W10</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A10),--(eLeave!$E:$E<=W$5),--(eLeave!$F:$F>=W$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">X10</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A10),--(eLeave!$E:$E<=X$5),--(eLeave!$F:$F>=X$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Y10</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A10),--(eLeave!$E:$E<=Y$5),--(eLeave!$F:$F>=Y$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Z10</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A10),--(eLeave!$E:$E<=Z$5),--(eLeave!$F:$F>=Z$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">AA10</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A10),--(eLeave!$E:$E<=AA$5),--(eLeave!$F:$F>=AA$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">AB10</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A10),--(eLeave!$E:$E<=AB$5),--(eLeave!$F:$F>=AB$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">O11</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A11),--(eLeave!$E:$E<=O$5),--(eLeave!$F:$F>=O$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">P11</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A11),--(eLeave!$E:$E<=P$5),--(eLeave!$F:$F>=P$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Q11</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A11),--(eLeave!$E:$E<=Q$5),--(eLeave!$F:$F>=Q$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">R11</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A11),--(eLeave!$E:$E<=R$5),--(eLeave!$F:$F>=R$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">S11</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A11),--(eLeave!$E:$E<=S$5),--(eLeave!$F:$F>=S$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">T11</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A11),--(eLeave!$E:$E<=T$5),--(eLeave!$F:$F>=T$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">U11</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A11),--(eLeave!$E:$E<=U$5),--(eLeave!$F:$F>=U$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">V11</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A11),--(eLeave!$E:$E<=V$5),--(eLeave!$F:$F>=V$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">W11</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A11),--(eLeave!$E:$E<=W$5),--(eLeave!$F:$F>=W$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">X11</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A11),--(eLeave!$E:$E<=X$5),--(eLeave!$F:$F>=X$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Y11</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A11),--(eLeave!$E:$E<=Y$5),--(eLeave!$F:$F>=Y$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">Z11</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A11),--(eLeave!$E:$E<=Z$5),--(eLeave!$F:$F>=Z$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">AA11</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A11),--(eLeave!$E:$E<=AA$5),--(eLeave!$F:$F>=AA$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">AB11</th><td style="text-align:left">=1-SUMPRODUCT(--(eLeave!$A:$A='Resource Tracker'!$A11),--(eLeave!$E:$E<=AB$5),--(eLeave!$F:$F>=AB$5),--(eLeave!$K:$K=eLeave!$L:$L)*0.5+--(eLeave!$K:$K<>eLeave!$L:$L))</td></tr></tbody></table></td></tr></tbody></table>

Below shows my source data which is extracted from out leave system, unfortunately I cannot control the way this is outputted from the system.

Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Concatenated Name</td><td style=";">Group</td><td style=";">FirstName</td><td style=";">Lastname</td><td style=";">LeaveFrom</td><td style=";">LeaveTo</td><td style=";">Booked on date</td><td style=";">Period</td><td style=";">LeaveInc</td><td style=";">LeaveType</td><td style=";">FAMPM</td><td style=";">TAMPM</td><td style=";">Status</td><td style=";">Team#</td><td style=";">Approved</td><td style=";">Disabled</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">Person 1</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">1</td><td style=";">Person </td><td style="text-align: right;;">26/04/2011</td><td style="text-align: right;;">28/04/2011</td><td style="text-align: right;;">21/12/2010</td><td style="text-align: right;;">24</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Person 1</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">1</td><td style=";">Person </td><td style="text-align: right;;">23/05/2011</td><td style="text-align: right;;">23/05/2011</td><td style="text-align: right;;">25/01/2011</td><td style="text-align: right;;">8</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">Person 1</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">1</td><td style=";">Person </td><td style="text-align: right;;">06/06/2011</td><td style="text-align: right;;">06/06/2011</td><td style="text-align: right;;">08/02/2011</td><td style="text-align: right;;">8</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">Person 1</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">1</td><td style=";">Person </td><td style="text-align: right;;">10/06/2011</td><td style="text-align: right;;">10/06/2011</td><td style="text-align: right;;">14/05/2011</td><td style="text-align: right;;">4</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">PM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">Person 1</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">1</td><td style=";">Person </td><td style="text-align: right;;">13/06/2011</td><td style="text-align: right;;">13/06/2011</td><td style="text-align: right;;">14/05/2011</td><td style="text-align: right;;">8</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">Person 1</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">1</td><td style=";">Person </td><td style="text-align: right;;">15/08/2011</td><td style="text-align: right;;">19/08/2011</td><td style="text-align: right;;">22/12/2010</td><td style="text-align: right;;">40</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style=";">Person 1</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">1</td><td style=";">Person </td><td style="text-align: right;;">22/08/2011</td><td style="text-align: right;;">25/08/2011</td><td style="text-align: right;;">22/12/2010</td><td style="text-align: right;;">32</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style=";">Person 1</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">1</td><td style=";">Person </td><td style="text-align: right;;">16/09/2011</td><td style="text-align: right;;">16/09/2011</td><td style="text-align: right;;">08/06/2011</td><td style="text-align: right;;">8</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style=";">Person 1</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">1</td><td style=";">Person </td><td style="text-align: right;;">19/09/2011</td><td style="text-align: right;;">19/09/2011</td><td style="text-align: right;;">08/06/2011</td><td style="text-align: right;;">8</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style=";">Person 2</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">2</td><td style=";">Person </td><td style="text-align: right;;">14/04/2011</td><td style="text-align: right;;">21/04/2011</td><td style="text-align: right;;">31/03/2011</td><td style="text-align: right;;">36</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">PM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style=";">Person 2</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">2</td><td style=";">Person </td><td style="text-align: right;;">02/05/2011</td><td style="text-align: right;;">02/05/2011</td><td style="text-align: right;;">03/05/2011</td><td style="text-align: right;;">8</td><td style=";">Hours</td><td style=";">Bank Holiday credit</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style=";">Person 2</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">2</td><td style=";">Person </td><td style="text-align: right;;">23/05/2011</td><td style="text-align: right;;">23/05/2011</td><td style="text-align: right;;">11/05/2011</td><td style="text-align: right;;">8</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style=";">Person 2</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">2</td><td style=";">Person </td><td style="text-align: right;;">27/05/2011</td><td style="text-align: right;;">27/05/2011</td><td style="text-align: right;;">24/05/2011</td><td style="text-align: right;;">8</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style=";">Person 2</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">2</td><td style=";">Person </td><td style="text-align: right;;">31/05/2011</td><td style="text-align: right;;">31/05/2011</td><td style="text-align: right;;">24/05/2011</td><td style="text-align: right;;">8</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">16</td><td style=";">Person 2</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">2</td><td style=";">Person </td><td style="text-align: right;;">28/06/2011</td><td style="text-align: right;;">01/07/2011</td><td style="text-align: right;;">31/03/2011</td><td style="text-align: right;;">32</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style=";">Person 2</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">2</td><td style=";">Person </td><td style="text-align: right;;">04/07/2011</td><td style="text-align: right;;">08/07/2011</td><td style="text-align: right;;">31/03/2011</td><td style="text-align: right;;">40</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style=";">Person 2</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">2</td><td style=";">Person </td><td style="text-align: right;;">23/08/2011</td><td style="text-align: right;;">26/08/2011</td><td style="text-align: right;;">12/04/2011</td><td style="text-align: right;;">32</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">19</td><td style=";">Person 4</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">4</td><td style=";">Person </td><td style="text-align: right;;">09/04/2011</td><td style="text-align: right;;">17/04/2011</td><td style="text-align: right;;">26/11/2010</td><td style="text-align: right;;">32</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">20</td><td style=";">Person 4</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">4</td><td style=";">Person </td><td style="text-align: right;;">22/04/2011</td><td style="text-align: right;;">28/04/2011</td><td style="text-align: right;;">09/12/2010</td><td style="text-align: right;;">16</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">21</td><td style=";">Person 4</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">4</td><td style=";">Person </td><td style="text-align: right;;">28/05/2011</td><td style="text-align: right;;">05/06/2011</td><td style="text-align: right;;">03/05/2011</td><td style="text-align: right;;">32</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">22</td><td style=";">Person 4</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">4</td><td style=";">Person </td><td style="text-align: right;;">23/07/2011</td><td style="text-align: right;;">29/07/2011</td><td style="text-align: right;;">18/04/2011</td><td style="text-align: right;;">40</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">23</td><td style=";">Person 4</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">4</td><td style=";">Person </td><td style="text-align: right;;">30/07/2011</td><td style="text-align: right;;">07/08/2011</td><td style="text-align: right;;">21/01/2011</td><td style="text-align: right;;">32</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">24</td><td style=";">Person 4</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">4</td><td style=";">Person </td><td style="text-align: right;;">27/08/2011</td><td style="text-align: right;;">04/09/2011</td><td style="text-align: right;;">26/11/2010</td><td style="text-align: right;;">24</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">25</td><td style=";">Person 4</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">4</td><td style=";">Person </td><td style="text-align: right;;">22/10/2011</td><td style="text-align: right;;">30/10/2011</td><td style="text-align: right;;">26/11/2010</td><td style="text-align: right;;">32</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">26</td><td style=";">Person 5</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">5</td><td style=";">Person </td><td style="text-align: right;;">01/04/2011</td><td style="text-align: right;;">01/04/2011</td><td style="text-align: right;;">10/12/2010</td><td style="text-align: right;;">8</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">27</td><td style=";">Person 5</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">5</td><td style=";">Person </td><td style="text-align: right;;">04/05/2011</td><td style="text-align: right;;">06/05/2011</td><td style="text-align: right;;">13/01/2011</td><td style="text-align: right;;">24</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">28</td><td style=";">Person 5</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">5</td><td style=";">Person </td><td style="text-align: right;;">04/07/2011</td><td style="text-align: right;;">08/07/2011</td><td style="text-align: right;;">14/05/2011</td><td style="text-align: right;;">40</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">29</td><td style=";">Person 5</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">5</td><td style=";">Person </td><td style="text-align: right;;">12/07/2011</td><td style="text-align: right;;">15/07/2011</td><td style="text-align: right;;">14/06/2011</td><td style="text-align: right;;">32</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">30</td><td style=";">Person 5</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">5</td><td style=";">Person </td><td style="text-align: right;;">06/09/2011</td><td style="text-align: right;;">16/09/2011</td><td style="text-align: right;;">14/05/2011</td><td style="text-align: right;;">72</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">31</td><td style=";">Person 6</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">6</td><td style=";">Person </td><td style="text-align: right;;">06/04/2011</td><td style="text-align: right;;">08/04/2011</td><td style="text-align: right;;">19/10/2010</td><td style="text-align: right;;">24</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">32</td><td style=";">Person 6</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">6</td><td style=";">Person </td><td style="text-align: right;;">20/05/2011</td><td style="text-align: right;;">24/05/2011</td><td style="text-align: right;;">19/10/2010</td><td style="text-align: right;;">16</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">33</td><td style=";">Person 6</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">6</td><td style=";">Person </td><td style="text-align: right;;">30/06/2011</td><td style="text-align: right;;">30/06/2011</td><td style="text-align: right;;">08/06/2011</td><td style="text-align: right;;">8</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">34</td><td style=";">Person 6</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">6</td><td style=";">Person </td><td style="text-align: right;;">12/07/2011</td><td style="text-align: right;;">13/07/2011</td><td style="text-align: right;;">08/06/2011</td><td style="text-align: right;;">16</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">35</td><td style=";">Person 6</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">6</td><td style=";">Person </td><td style="text-align: right;;">05/08/2011</td><td style="text-align: right;;">05/08/2011</td><td style="text-align: right;;">03/06/2011</td><td style="text-align: right;;">8</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">36</td><td style=";">Person 6</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">6</td><td style=";">Person </td><td style="text-align: right;;">23/08/2011</td><td style="text-align: right;;">24/08/2011</td><td style="text-align: right;;">08/06/2011</td><td style="text-align: right;;">16</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">37</td><td style=";">Person 6</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">6</td><td style=";">Person </td><td style="text-align: right;;">31/08/2011</td><td style="text-align: right;;">31/08/2011</td><td style="text-align: right;;">08/06/2011</td><td style="text-align: right;;">8</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">38</td><td style=";">Person 6</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">6</td><td style=";">Person </td><td style="text-align: right;;">15/09/2011</td><td style="text-align: right;;">21/09/2011</td><td style="text-align: right;;">03/06/2011</td><td style="text-align: right;;">40</td><td style=";">Hours</td><td style=";">Annual Leave</td><td style=";">AM</td><td style=";">PM</td><td style=";">Approved</td><td style="text-align: right;;">123456789</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr></tbody></table>
eLeave​




I would like to find an alternate way which would not be as processing hungry if anyone has any ideas? many thanks.
 
Upvote 0
Try this:

Code: 
=1-SUMPRODUCT(
[COLOR=blue]--([COLOR=red]OFFSET([COLOR=green]eLeave!$A$1,0,0,COUNTA([COLOR=purple]eLeave!$A:$A[/COLOR])[/COLOR])='Resource Tracker'!$A6[/COLOR]),[/COLOR]
[COLOR=blue]--([COLOR=red]OFFSET([COLOR=green]eLeave!$E$1,0,0,COUNTA([COLOR=purple]eLeave!$E:$E[/COLOR])[/COLOR])<=N$5[/COLOR]),[/COLOR]
[COLOR=blue]--([COLOR=red]OFFSET([COLOR=green]eLeave!$F$1,0,0,COUNTA([COLOR=purple]eLeave!$F:$F[/COLOR])[/COLOR])>=N$5[/COLOR]), [/COLOR]
[COLOR=blue]--([COLOR=red]OFFSET([COLOR=green]eLeave!$K$1,0,0,COUNTA([COLOR=purple]eLeave!$K:$K[/COLOR])[/COLOR])=[/COLOR][/COLOR]
[COLOR=blue][COLOR=red]OFFSET([COLOR=green]eLeave!$L$1,0,0,COUNTA([COLOR=purple]eLeave!$L:$L[/COLOR])[/COLOR])[/COLOR])*0.5+[/COLOR]
[COLOR=blue]--([COLOR=red]OFFSET([COLOR=green]eLeave!$K$1,0,0,COUNTA([COLOR=purple]eLeave!$K:$K[/COLOR])[/COLOR])<>[/COLOR][/COLOR]
[COLOR=blue][COLOR=red]OFFSET([COLOR=green]eLeave!$L$1,0,0,COUNTA([COLOR=purple]eLeave!$L:$L[/COLOR])[/COLOR])[/COLOR])[/COLOR])

Markmzz
 
Upvote 0
markmzz said:
Try this:

Code: 
=1-SUMPRODUCT(
[COLOR=blue]--([COLOR=red]OFFSET([COLOR=green]eLeave!$A$1,0,0,COUNTA([COLOR=purple]eLeave!$A:$A[/COLOR])[/COLOR])='Resource Tracker'!$A6[/COLOR]),[/COLOR]
[COLOR=blue]--([COLOR=red]OFFSET([COLOR=green]eLeave!$E$1,0,0,COUNTA([COLOR=purple]eLeave!$E:$E[/COLOR])[/COLOR])<=N$5[/COLOR]),[/COLOR]
[COLOR=blue]--([COLOR=red]OFFSET([COLOR=green]eLeave!$F$1,0,0,COUNTA([COLOR=purple]eLeave!$F:$F[/COLOR])[/COLOR])>=N$5[/COLOR]), [/COLOR]
[COLOR=blue]--([COLOR=red]OFFSET([COLOR=green]eLeave!$K$1,0,0,COUNTA([COLOR=purple]eLeave!$K:$K[/COLOR])[/COLOR])=[/COLOR][/COLOR]
[COLOR=blue][COLOR=red]OFFSET([COLOR=green]eLeave!$L$1,0,0,COUNTA([COLOR=purple]eLeave!$L:$L[/COLOR])[/COLOR])[/COLOR])*0.5+[/COLOR]
[COLOR=blue]--([COLOR=red]OFFSET([COLOR=green]eLeave!$K$1,0,0,COUNTA([COLOR=purple]eLeave!$K:$K[/COLOR])[/COLOR])<>[/COLOR][/COLOR]
[COLOR=blue][COLOR=red]OFFSET([COLOR=green]eLeave!$L$1,0,0,COUNTA([COLOR=purple]eLeave!$L:$L[/COLOR])[/COLOR])[/COLOR])[/COLOR])
Markmzz
Click to expand...

markmzz Works Brilliantly! It's pretty much instant! Thank You Very Much

Marcelo Branco - Thanks for your help with this too :)
 
Upvote 0
jdawg_1989 said:
markmzz Works Brilliantly! It's pretty much instant! Thank You Very Much

Marcelo Branco - Thanks for your help with this too :)
Click to expand...

Tks for your feedback

M.
ps: try to avoid the use of references to the entire column as
eLeave!$A:$A='Resource Tracker'!$A6

Problably that was the reason for a so bad performance - Excel 2007 or higher has more than 1 million rows...

Use something like
eLeave!$A2:$A10000='Resource Tracker'!$A6
 
Upvote 0
You must log in or register to reply here.

Similar threads

SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
165
SFCChase
SFCChase
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
400
dshtesy
D
J
  • Question Question
XLOOKUP, Finding a Value based on Time and Date
Replies
1
Views
377
SunnyAlv
SunnyAlv
S
  • Solved
Lookup With Gaps
Replies
5
Views
223
Steve 1962
S
N
  • Question Question
Advisor login, logout & break calculation
Replies
1
Views
253
Cubist
Cubist

Forum statistics

Threads
1,224,598
Messages
6,179,818
Members
452,946
Latest member
JoseDavid

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top