building a smart league table
 

Hi,

What would be the best way of building a league table from 75+ teams that automatcially updates itself when I update player scores. The set up of each team looks like this (beginning at A1) with each team recurring every 20th row. Player scores are linked to main players table using vloopup =IF($A2="","",VLOOKUP($A2,Euro2004Players,5,1))

Tony (Manager name occurs in first row, i.e. A1)
Code Player Team Value Point
8 Kahn Germany 4 6
137 Cocu Holland 4
104 R Kovac Croatia 3.5
114 Jensen Denmark 3
132 Worns Germany 4
226 Vieira France 6
202 Petrov Bulgaria 3
250 Astaf Latvia 2.5
321 Henry France 8.5
345 Postiga Portugal 3.5
335 Nistroo Holland 8
Total 50 1

The idea being that my league table would end up looking something like this

Pos Manager Pt
1 Tony 6
2 Chris 5

Very grateful for any ideas or code on how to put this together

Ton

building a smart league table
 

Tony,

speaking personally, the explanation does not make it clear to me.

I assume that each person has twenty players, but what are the numbers
before and after the players meaning, and how does that translate to say the
69 for Tony?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tony" wrote in message
...
Hi,

What would be the best way of building a league table from 75+ teams that
automatcially updates itself when I update player scores. The set up of
each team looks like this (beginning at A1) with each team recurring every
20th row. Player scores are linked to main players table using vloopup
=IF($A2="","",VLOOKUP($A2,Euro2004Players,5,1)).

Tony (Manager name occurs in first row, i.e. A1)
Code Player Team Value Points
8 Kahn Germany 4 69
137 Cocu Holland 4 0
104 R Kovac Croatia 3.5 0
114 Jensen Denmark 3 0
132 Worns Germany 4 0
226 Vieira France 6 0
202 Petrov Bulgaria 3 0
250 Astaf Latvia 2.5 0
321 Henry France 8.5 0
345 Postiga Portugal 3.5 0
335 Nistroo Holland 8 0
Total 50 10

The idea being that my league table would end up looking something like
this.

Pos Manager Pts
1 Tony 69
2 Chris 50

Very grateful for any ideas or code on how to put this together.

Tony

building a smart league table
 

Hi

The setup you delivered to us isn't very comprehensible. But my advice is -
for such tasks it's better when you stick to database-type design.

I.e. you create a table Players situated on sheet Players, where all info
like Name, Team, Orign, etc. is placed - a row for every player.
Then you create a table Teams, situated on sheet Teams, where all general
info like Name, Manager, ets is placed - again a row for every team.
Now you probably need a table (again on separate sheet), where you register
some kind of scores.
Then you can add sone columns to Players and/or Teams table(s), where you
calculate some values, based on Scores table. And in Teams table, maybe also
you need calculate some values based on team members data on Players sheet.
And at last, you create (a) report sheet(s), where you select a team, and
all needed info like manager name, team members, their scores, team overall
score etc. is calculated. And probably you need a report sheet which
displays overall standings for all teams too.


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)



"Tony" wrote in message
...
Hi,

What would be the best way of building a league table from 75+ teams that
automatcially updates itself when I update player scores. The set up of
each team looks like this (beginning at A1) with each team recurring every
20th row. Player scores are linked to main players table using vloopup
=IF($A2="","",VLOOKUP($A2,Euro2004Players,5,1)).

Tony (Manager name occurs in first row, i.e. A1)
Code Player Team Value Points
8 Kahn Germany 4 69
137 Cocu Holland 4 0
104 R Kovac Croatia 3.5 0
114 Jensen Denmark 3 0
132 Worns Germany 4 0
226 Vieira France 6 0
202 Petrov Bulgaria 3 0
250 Astaf Latvia 2.5 0
321 Henry France 8.5 0
345 Postiga Portugal 3.5 0
335 Nistroo Holland 8 0
Total 50 10

The idea being that my league table would end up looking something like
this.

Pos Manager Pts
1 Tony 69
2 Chris 50

Very grateful for any ideas or code on how to put this together.

Tony

building a smart league table
 

Hi Bob

Thank you for your reply
My apologies for the confusion caused by the total points scenario
I wish to run a fantasy football league for Euro 2004. At the minute I have 75+ teams and I have laid them out on a sheet called 'Teams'. Each team has 11 players and, for consistency, I have added each new team starting at every 20th row.
The values I supplied are Player Code, Player Name, Player Team, Player Value and Player Points. Each team entered into the competition is not allowed to exceed £50m and players are awarded points for their performance during the tournament. As Euro 2004 hasn't started yet I had to put dummy points in (69) for my first player show how the scoring system will work. I can confirm that my dummy total at the minute is 69 (and not 10 as in my earlier post)

Is there a smart way for Excel to build a league table that goes down through each team and return Rank, Manager Name and Total Points scored for each team in my League, i.e. something like this..

Rank Manager Total Point
1 Tony 6
2 Chris 5

Hope this helps
Many thanks

building a smart league table
 

Tony,

I have knocked up a little spreadsheet to show results. Give me your email
address and I will send it to you.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tony" wrote in message
...
Hi Bob,

Thank you for your reply.
My apologies for the confusion caused by the total points scenario.
I wish to run a fantasy football league for Euro 2004. At the minute I
have 75+ teams and I have laid them out on a sheet called 'Teams'. Each
team has 11 players and, for consistency, I have added each new team
starting at every 20th row.
The values I supplied are Player Code, Player Name, Player Team, Player
Value and Player Points. Each team entered into the competition is not
allowed to exceed £50m and players are awarded points for their performance
during the tournament. As Euro 2004 hasn't started yet I had to put dummy
points in (69) for my first player show how the scoring system will work. I
can confirm that my dummy total at the minute is 69 (and not 10 as in my
earlier post).

Is there a smart way for Excel to build a league table that goes down
through each team and return Rank, Manager Name and Total Points scored for
each team in my League, i.e. something like this...

Rank Manager Total Points
1 Tony 69
2 Chris 50


Hope this helps.
Many thanks

building a smart league table
 

Bob,

Great stuff - much appreciated.
I will send you an email.

Regards

Tony


-----Original Message-----
Tony,

I have knocked up a little spreadsheet to show results.
Give me your email
address and I will send it to you.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tony" wrote in
message
...
Hi Bob,

Thank you for your reply.
My apologies for the confusion caused by the total
points scenario.
I wish to run a fantasy football league for Euro 2004.
At the minute I
have 75+ teams and I have laid them out on a sheet
called 'Teams'. Each
team has 11 players and, for consistency, I have added
each new team
starting at every 20th row.
The values I supplied are Player Code, Player Name,
Player Team, Player
Value and Player Points. Each team entered into the
competition is not
allowed to exceed £50m and players are awarded points for
their performance
during the tournament. As Euro 2004 hasn't started yet I
had to put dummy
points in (69) for my first player show how the scoring
system will work. I
can confirm that my dummy total at the minute is 69 (and
not 10 as in my
earlier post).

Is there a smart way for Excel to build a league table
that goes down
through each team and return Rank, Manager Name and Total
Points scored for
each team in my League, i.e. something like this...

Rank Manager Total Points
1 Tony 69
2 Chris 50


Hope this helps.
Many thanks


.