Index and match help

C

cocks17

New Member
Joined
Dec 20, 2010
Messages
36
I am trying to figure out how I can look up when some activity stopped and then report back on the date it stopped. I think I need to use index and match but I could really use some help. My data and ideal output table is attached in an image to this post. I'd like to only have activity which has stopped to appear, so if there has been activity in the last 2 days then this is considered active.

Please let me know your thoguhts on a formula for the table or if a seperate sheet may need to be created? :confused:

excelstoppedquestion.jpg


Cheers
Paul
 
Last edited:
Sort by date Sort by votes
****s17 said:
Thank you! Were really cooking now on this one! incredible! VBA module worked a treat! So one last ask on this. Say for instance we want to add another dimension to this and we only want to show this month and if its not this month then its blank?
Click to expand...

That would be...

Code: 
=IF(COUNT(INDEX($C$5:$J$7,MATCH(L4,$B$5:$B$7,0),0))>=2,
    IF(V(TEXT(LOOKUP(9.99999999999999E+307,
        INDEX($C$5:$J$7,MATCH(L4,$B$5:$B$7,0),0),
        $C$4:$J$4),"mmmyy"))=TEXT(TODAY(),"mmmyy"),V(),""),
     "")
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
****s17 said:
Is it possible to make this recognise weather it is this months only? as the date line can be a lot longer than my example above.

Thanks
Paul
Click to expand...

****s17,

I think it is ok.

But, test the formula and give us a feedback.

Markmzz
 
Upvote 0
markmzz said:
****s17,

I think it is ok.

But, test the formula and give us a feedback.

Markmzz
Click to expand...


HI markmzz

Apologies i think there was confusion over what I was asking for. i wondered if it were possible to show only those activities which have stopped in the current month and the date they stopped, rather than displaying the the actual month name. The last formula you sent over give me the month name e.g. "May11".

Let me know if im not making sense?
 
Upvote 0
****s17 said:
HI markmzz

Apologies i think there was confusion over what I was asking for. i wondered if it were possible to show only those activities which have stopped in the current month and the date they stopped, rather than displaying the the actual month name. The last formula you sent over give me the month name e.g. "May11".

Let me know if im not making sense?
Click to expand...

****s17,

I think this solve your problem. Try this (look at my example):

1) Create the name TodayT pointing to the cell Q2 (=$Q$2).

2) With the cell O4 selected (this is very important), create the name ACTDATE with the following formula (formula array – press Ctrl + Shift + Enter and not only Enter):

=IF(SUM((N4=$B$5:$B$10)*(INDEX($C$5:$L$10,,MATCH(TodayT,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-1,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-2,$C$4:$L$4))="")),INDEX($C$4:$L$4,COUNT($C5:$L5)),"")

3) Again with the cell O4 selected, type the following normal formula:

=IF(TEXT(ACTDATE,"mmm/aa")=TEXT(TodayT,"mmm/aa"),ACTDATE,"")

4) Copy the formula for the interval O5:O9

OBS: After testing this formula change Q2 value cell to =Today().

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; 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></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Data</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: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Output</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Day TodayT</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">05/06/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Date</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Activity</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Date Stopped</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">TodayT</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">27/05/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">28/05/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">29/05/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">30/05/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">31/05/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">01/06/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">02/06/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">03/06/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">04/06/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">05/06/2011</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Activity 1</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">28/05/2011</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Activity 1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">102</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">88</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Activity 2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">02/06/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">02/06/2011</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Activity 2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">87</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">65</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">43</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">89</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">9</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">9</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">78</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Activity 3</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Activity 3</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">97</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">87</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">87</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">102</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">95</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">103</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">97</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">86</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">87</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">88</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Activity 4</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Activity 4</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">107</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">86</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">105</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">104</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">80</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">56</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">32</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">65</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Activity 5</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Activity 5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">100</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">93</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">83</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">73</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">63</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">53</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">43</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">33</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Activity 6</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">01/06/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">01/06/2011</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #92d050; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Activity 6</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">98</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">107</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">106</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">106</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">100</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">88</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>O4</TH><TD style="TEXT-ALIGN: left">=IF(TEXT(ACTDATE,"mmm/aa")=TEXT(TodayT,"mmm/aa"),ACTDATE,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>O5</TH><TD style="TEXT-ALIGN: left">=IF(TEXT(ACTDATE,"mmm/aa")=TEXT(TodayT,"mmm/aa"),ACTDATE,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>O6</TH><TD style="TEXT-ALIGN: left">=IF(TEXT(ACTDATE,"mmm/aa")=TEXT(TodayT,"mmm/aa"),ACTDATE,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>O7</TH><TD style="TEXT-ALIGN: left">=IF(TEXT(ACTDATE,"mmm/aa")=TEXT(TodayT,"mmm/aa"),ACTDATE,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>O8</TH><TD style="TEXT-ALIGN: left">=IF(TEXT(ACTDATE,"mmm/aa")=TEXT(TodayT,"mmm/aa"),ACTDATE,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>O9</TH><TD style="TEXT-ALIGN: left">=IF(TEXT(ACTDATE,"mmm/aa")=TEXT(TodayT,"mmm/aa"),ACTDATE,"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>P4</TH><TD style="TEXT-ALIGN: left">{=IF(SUM((N4=$B$5:$B$10)*(INDEX($C$5:$L$10,,MATCH(TodayT,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-1,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-2,$C$4:$L$4))="")),INDEX($C$4:$L$4,COUNT($C5:$L5)),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>P5</TH><TD style="TEXT-ALIGN: left">{=IF(SUM((N5=$B$5:$B$10)*(INDEX($C$5:$L$10,,MATCH(TodayT,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-1,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-2,$C$4:$L$4))="")),INDEX($C$4:$L$4,COUNT($C6:$L6)),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>P6</TH><TD style="TEXT-ALIGN: left">{=IF(SUM((N6=$B$5:$B$10)*(INDEX($C$5:$L$10,,MATCH(TodayT,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-1,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-2,$C$4:$L$4))="")),INDEX($C$4:$L$4,COUNT($C7:$L7)),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>P7</TH><TD style="TEXT-ALIGN: left">{=IF(SUM((N7=$B$5:$B$10)*(INDEX($C$5:$L$10,,MATCH(TodayT,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-1,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-2,$C$4:$L$4))="")),INDEX($C$4:$L$4,COUNT($C8:$L8)),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>P8</TH><TD style="TEXT-ALIGN: left">{=IF(SUM((N8=$B$5:$B$10)*(INDEX($C$5:$L$10,,MATCH(TodayT,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-1,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-2,$C$4:$L$4))="")),INDEX($C$4:$L$4,COUNT($C9:$L9)),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>P9</TH><TD style="TEXT-ALIGN: left">{=IF(SUM((N9=$B$5:$B$10)*(INDEX($C$5:$L$10,,MATCH(TodayT,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-1,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-2,$C$4:$L$4))="")),INDEX($C$4:$L$4,COUNT($C10:$L10)),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>P10</TH><TD style="TEXT-ALIGN: left">{=IF(SUM((N10=$B$5:$B$10)*(INDEX($C$5:$L$10,,MATCH(TodayT,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-1,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-2,$C$4:$L$4))="")),INDEX($C$4:$L$4,COUNT($C11:$L11)),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>P11</TH><TD style="TEXT-ALIGN: left">{=IF(SUM((N11=$B$5:$B$10)*(INDEX($C$5:$L$10,,MATCH(TodayT,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-1,$C$4:$L$4))="")*(INDEX($C$5:$L$10,,MATCH(TodayT-2,$C$4:$L$4))="")),INDEX($C$4:$L$4,COUNT($C12:$L12)),"")}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself







</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Workbook Defined Names<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Name</TH><TH style="TEXT-ALIGN: left">Refers To</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>ACTDATE</TH><TD style="TEXT-ALIGN: left">=IF(SUM((Month!N4=Month!$B$5:$B$10)*(INDEX(Month!$C$5:$L$10,,MATCH(TodayT,Month!$C$4:$L$4))="")*(INDEX(Month!$C$5:$L$10,,MATCH(TodayT-1,Month!$C$4:$L$4))="")*(INDEX(Month!$C$5:$L$10,,MATCH(TodayT-2,Month!$C$4:$L$4))="")),INDEX(Month!$C$4:$L$4,COUNT(Month!$C5:$L5)),"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>TodayT</TH><TD style="TEXT-ALIGN: left">=Month!$Q$2</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Index with match and (maybe) offset?
2
Replies
10
Views
476
Peter_SSs
Peter_SSs
B
  • Solved
Index match where duplicates exist
Replies
3
Views
609
Fluff
Fluff
E
  • Question Question
Using Index Match to find the month the value occurs in
Replies
5
Views
184
Cubist
Cubist
B
  • Solved
Return Last Value in Range Using INDEX MATCH
Replies
3
Views
422
Alex Blakenburg
A
S
  • Solved
IFS INDEX and MATCH based on Dropdown List
Replies
4
Views
414
bobsan42
bobsan42

Forum statistics

Threads
1,225,156
Messages
6,183,233
Members
453,152
Latest member
ChrisMd

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