Tough Golf Tracker

[lordturl]

Hi guys,

I could really appreciate some help tracking some golf matchplay results between me and 3 friends. Each month we play a team competition 2vs2, randomly picked teams, with the lowest score from the team being used. Whichever team has the lowest score wins the hole, if they're the same score then the hole is tied. Most holes won after 18 are the winning team.

How can I get columns W & Y to pick the lowest score from the Team members in AB & AC, then in X & Z show a Win Lose or Draw? Row 24 would then show number of Wins.

I would like to do the same in columns E and P, to show which whose score won the hole, but I think this could get complicated - e.g. if score is less then this this or this, then "Win" but if equal then "Draw, and if less then "Lose". Too much going on?

It would be great if I could then duplicate this sheet each month, but just change the names around in AB & AC - is that possible? Maybe I need to layout the data differently?

Thanks in advance!!

 

[mrshl9898]

First, i'd have

A4 = AB6
L4 = AB7
A26 = AC6
L26 = AC7

So the names don't get mixed up.

Then,

W6 = MIN(C6,N6) copied down
Y6 = MIN(C28,N28) copied down
X6 = IF(W6>Y6,"Lose",IF(W6=Y6,"Draw","Win")) copied down
Z6 = IF(W6>Y6,"Win",IF(W6=Y6,"Draw","Lose")) copied down
X24 = COUNTIF(X6:X23,"Win")
Z24 = COUNTIF(Z6:Z23,"Win")

 

[lordturl]

First, i'd have

A4 = AB6
L4 = AB7
A26 = AC6
L26 = AC7

So the names don't get mixed up.

Then,

W6 = MIN(C6,N6) copied down
Y6 = MIN(C28,N28) copied down
X6 = IF(W6>Y6,"Lose",IF(W6=Y6,"Draw","Win")) copied down
Z6 = IF(W6>Y6,"Win",IF(W6=Y6,"Draw","Lose")) copied down
X24 = COUNTIF(X6:X23,"Win")
Z24 = COUNTIF(Z6:Z23,"Win")

Thank you so much - this is amazing! I've used this to make a great sheet for every round we play. It really works so well!

Now I'd like to make a front sheet summary with collective stats. Ideally I would like this to count total number of shots per player, number of birdie/pars, number of wins/draws/losses etc, and loads of other stats I can pull from each round.

Firstly, can I get the summary page to search below per player name, I have laid out each sheet so that the player name moves depending on the random team drawn - will this make this difficult?

Secondly, can I create dynamic leaderboards on the front sheet? e.g. the least number of shots hit per player sits at the top of the table?

Lastly, everytime I add a sheet, will I have to manually update the formulas? Or can I get it to automatically update everytime I add a sheet if the value is in the same cell each time?

Thanks again!

 

[mrshl9898]

For the dynamic leaderboards you could use SMALL'
'
eg.

SMALL(D26,O26,D49,O49,1) gives the lowest score ,2 second lowest etc..

Then add it to an if, seeing where the score is and returning the player name above it.

IF(SMALL(D26,O26,D49,O49,1)=D26,B6,IF(SMALL(D26,O26,D49,O49,1)=O26,B29, IF(SMALL(D26,O26,D49,O49,1)=D49,M6,M29)))


Though, in saying this not sure how you'd handle a tie...