building a smart league table

[Tony]

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

 

[Bob Phillips]

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)

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)).

 

[Arvi Laanemets]

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<At>tarkon.ee)

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]

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

 

[Bob Phillips]

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)

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...

 

[Tony]

Bob,

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

Regards

Tony