Formula Help

[steeleaska]

I'm trying to write a formula to let me see the date a person last attended a meeting but I'm getting stuck.

say the person attending the first meeting a date is entered in to the cell the part that I'm getting stuck on is what forluma would i use if he attending the second meeting to overwrite the previous date.

Not sure if this make sense?

Example

Date of Last Meeting 11th April

What formula calculation is need to overwrite this date with the next meeting on 12th July?

Many, Many Thanks

 

[Matty]

Welcome to the Board...

Perhaps a set up like:


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 172px"><COL style="WIDTH: 19px"><COL style="WIDTH: 64px"><COL style="WIDTH: 200px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Person</TD><TD>Date of Meeting Attended</TD><TD></TD><TD>Person</TD><TD>Date of Last Meeting Attended</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Bob</TD><TD style="TEXT-ALIGN: right">01/01/2010</TD><TD></TD><TD>Bob</TD><TD style="TEXT-ALIGN: right">01/01/2010</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Terry</TD><TD style="TEXT-ALIGN: right">01/01/2010</TD><TD></TD><TD>Terry</TD><TD style="TEXT-ALIGN: right">01/04/2010</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Frank</TD><TD style="TEXT-ALIGN: right">01/01/2010</TD><TD></TD><TD>Frank</TD><TD style="TEXT-ALIGN: right">01/01/2010</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>John</TD><TD style="TEXT-ALIGN: right">01/01/2010</TD><TD></TD><TD>John</TD><TD style="TEXT-ALIGN: right">01/04/2010</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>John</TD><TD style="TEXT-ALIGN: right">01/04/2010</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Terry</TD><TD style="TEXT-ALIGN: right">01/04/2010</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>

Formula in E2 is:

=MAX(IF(A$2:A$7=D2,B$2:B$7))

Which is an array formula and therefore needs committing with Ctrl+Shift+Enter...

Matty

 

[steeleaska]

Thanks Matty

The way I have my Spreadsheet set up is

A1 - Name
B2 - Area
C3 - Last Meeting Attended
D4 - Meeting 11th May
E5 - Meeting 12th July

So in C3 the date will be the entry in D4 if he didn't attend I want the date in E5 if he did attend both meetings I would like the date in D4 to be updated with E5 date.

Is this possible or am I making things complicated?

 

[Matty]

In that case, C3 would simply be:

=IF(E5="",D4,E5)

Is that what you're after?

Matty

 

[steeleaska]

I'm starting to get myself confussed.

At the moment I have

=IF(F5="Yes","25th March",IF(F5="Aps","""))

which is working until I have to add the next meeting for 12th July it will not over write the date

 

[Matty]

You didn't mention the relevance of F5 in your previous posts.

Could you supply a view of all relevant data and detail exactly what you want the formula to deliver from this data?

Matty

 

[steeleaska]

<TABLE style="WIDTH: 501pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=667 border=0 x:str><COLGROUP><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4900" width=134><COL style="WIDTH: 162pt; mso-width-source: userset; mso-width-alt: 7899" width=216><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 101pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" width=134 height=22></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 162pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=216></TD><TD class=xl46 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 94pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=125>Meetings February/March 2010</TD><TD class=xl53 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Meetings May 2010</TD><TD class=xl87 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Meetings July/August 2010</TD><TD class=xl53 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Meetings October 2010</TD></TR><TR style="HEIGHT: 48.75pt; mso-height-source: userset" height=65><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 48.75pt; BACKGROUND-COLOR: transparent" height=65>Name</TD><TD class=xl45 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 162pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=216>Last Meeting</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc">25th Mar</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc">11th May</TD><TD class=xl38 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc">22nd Jul</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc">12th Oct</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>NAME</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"><S style="VISIBILITY: hidden; mso-ignore: visibility"> </S></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Aps</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Yes</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver">Yes</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver"> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>NAME</TD><TD class=xl44 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> </TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc">Yes</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffcc">Aps</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Yes</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>NAME</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"><S style="VISIBILITY: hidden; mso-ignore: visibility"> </S></TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">YES</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">RES</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver">RES</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver"> </TD></TR></TBODY></TABLE>

 

[Matty]

OK, if I follow your set up correctly, try:

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 38px"><COL style="WIDTH: 75px"><COL style="WIDTH: 204px"><COL style="WIDTH: 128px"><COL style="WIDTH: 176px"><COL style="WIDTH: 153px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Meetings February/March 2010</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Meetings May 2010</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Meetings July/August 2010</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Meetings October 2010</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Name</TD><TD style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Last Meeting</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">25th Mar</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">11th May</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">22nd Jul</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">12th Oct</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 10pt">Bob</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">22nd Jul</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Aps</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Yes</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Yes</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 10pt">Terry</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">22nd Jul</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Yes</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Aps</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Yes</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 10pt">Frank</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">25th Mar</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Yes</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">RES</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">RES</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD></TR></TBODY></TABLE>

Formula in B3 is:

=INDEX(C$2:F$2,,MATCH(2,1/(C3:F3="Yes"),1))

Which needs committing with Ctrl+Shift+Enter, and can then be dragged down...

Matty

 

[steeleaska]

<TABLE style="WIDTH: 515pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=687 border=0><COLGROUP><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5083" width=139><COL style="WIDTH: 123pt; mso-width-source: userset; mso-width-alt: 5997" width=164><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 51.75pt" height=69><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 104pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 51.75pt; BACKGROUND-COLOR: white" width=139 height=69></TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 123pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=164></TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Meetings February/March 2010</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Meetings May 2010</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Meetings July/August 2010</TD><TD class=xl72 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Meetings October 2010</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 104pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=139 height=20>Name</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 123pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=164>Last Meeting</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>25th Mar</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>11th May</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>22nd Jul</TD><TD class=xl73 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>12th Oct</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 104pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=139 height=20>Bob</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 123pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" align=middle width=164>#VALUE!</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Abs</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Yes</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Abs</TD><TD class=xl74 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Yes</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 104pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=139 height=20>Terry</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 123pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=164></TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Abs</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Abs</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Yes</TD><TD class=xl74 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Abs</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl70 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 104pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" width=139 height=21>Frank</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 123pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=164></TD><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=64>Abs</TD><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=64>Yes</TD><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=64>Yes</TD><TD class=xl75 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white">Abs</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
Tried the formula but it keeps coming up with an error and I don't understand the MATCH Function.

Sorry to be a pain.

 

[Matty]

See my comment:

Which needs committing with Ctrl+Shift+Enter, and can then be dragged down...

Matty