Need Help :-(

[bboy]

Hi All,

Im having issues and its sending my head crazy lol.

I need a Vlook up to check if a player has played one of the below events (the list of names would be on a separate tabs under each event name which i have already set) and it to return a yes or a no. Then i need the consecutive column to return the amount of consectutive tournaments the player has participated in. Then i need the Expected prize column to check a seperate table for example - if the player plays 4 consecutive events they win 1 token.

<TABLE style="WIDTH: 677pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=902><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 48pt" span=8 width=64><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6838" width=187><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #e2e2e2; WIDTH: 68pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" height=20 width=90>Nicknames</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #e2e2e2; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>Event1</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #e2e2e2; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>Event2</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #e2e2e2; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>Event3</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #e2e2e2; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>Event4</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #e2e2e2; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>Event5</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #e2e2e2; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>Event6</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #e2e2e2; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>Event7</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #e2e2e2; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>Event8</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #e2e2e2; WIDTH: 140pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=187>Consecutive Tournaments</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #e2e2e2; WIDTH: 85pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 700; BORDER-RIGHT: #e2e2e2; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=113>Expected Prize</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #e2e2e2; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=20>Player1</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #e2e2e2; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>0</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #e2e2e2; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>0</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #e2e2e2; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>0</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #e2e2e2; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>0</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #e2e2e2; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>0</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #e2e2e2; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>0</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #e2e2e2; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>0</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #e2e2e2; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>0</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #e2e2e2; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none"></TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #e2e2e2; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #e2e2e2; FONT-WEIGHT: 400; BORDER-RIGHT: #e2e2e2; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none"></TD></TR></TBODY></TABLE>

can any one help me on this or point me in the right direction? Please let me know if you need further info

 

[P_Guru]

My best suggestion would be one cell to concatenate the Y/N answers (and do it as single characters, not words), then judicious use of nested FIND to compare first Y and first N, first Y after first N, etc. Tack a MAX on the front and it should work.

I did make a start but it became a very big formula, and not very clean. I think it's achievable though.

 

[bboy]

Sounds really complicated lol Thanks for the help think i will just have to do it manually :-S