I'm running a fantasy football league at work and i'm updating it via excel.
The system requires you to add points for each player in each team and a
total is calculated. I would then like to create a league table to show which
team is leading... it needs to update as I enter in the new points for each
player. I know it's really simple but I can't for the life of me remember how
to do it.

Whizz Wrote:
I'm running a fantasy football league at work and i'm updating it via
excel.
The system requires you to add points for each player in each team and
a
total is calculated. I would then like to create a league table to show
which
team is leading... it needs to update as I enter in the new points for
each
player. I know it's really simple but I can't for the life of me
remember how
to do it.

I do not know how your worksheet looks like but you might be needing
some helper columns for this.

For example, the team names may be summarily entered in Column G (from
G1 to G10, assuming you have 10 teams in your fantasy league). From H1
to H10 are entered the total scores for each corresponding team.

Based on the entries in Columns G and H, you can then RANK the teams.
Check out *www.cpearson.com/excel/rank.htm * for an excellent
explanation with sample applications regarding this topic.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=399736

Sample data might help, but it just sounds like SUMIF

=SUMIF(A1:A100,"Team A",B1:B100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Whizz" wrote in message
...
I'm running a fantasy football league at work and i'm updating it via
excel.
The system requires you to add points for each player in each team and a
total is calculated. I would then like to create a league table to show
which
team is leading... it needs to update as I enter in the new points for
each
player. I know it's really simple but I can't for the life of me remember
how
to do it.

Here you go:

http://www.megaupload.com/?d=27CUS14C

If you scroll down to H211 you'll see my league table but I haven't a clue
how to live update it so that when I update the players scores it updates the
manager's league table.

Thanks for your help

"Bob Phillips" wrote:

Sample data might help, but it just sounds like SUMIF

=SUMIF(A1:A100,"Team A",B1:B100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Whizz" wrote in message
...
I'm running a fantasy football league at work and i'm updating it via
excel.
The system requires you to add points for each player in each team and a
total is calculated. I would then like to create a league table to show
which
team is leading... it needs to update as I enter in the new points for
each
player. I know it's really simple but I can't for the life of me remember
how
to do it.

I can see the spreadsheet mate, but I have no idea of what to do. The cell
L212 refers to F21, what exactly should it be calculating.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Whizz" wrote in message
...
Here you go:

http://www.megaupload.com/?d=27CUS14C

If you scroll down to H211 you'll see my league table but I haven't a clue
how to live update it so that when I update the players scores it updates
the
manager's league table.

Thanks for your help

"Bob Phillips" wrote:

Sample data might help, but it just sounds like SUMIF

=SUMIF(A1:A100,"Team A",B1:B100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Whizz" wrote in message
...
I'm running a fantasy football league at work and i'm updating it via
excel.
The system requires you to add points for each player in each team and
a
total is calculated. I would then like to create a league table to
show
which
team is leading... it needs to update as I enter in the new points for
each
player. I know it's really simple but I can't for the life of me
remember
how
to do it.

Hi Whizz

Not quite the way I have set up a Fantasy League table for my son (Arsenal
supporter), as you are having to enter the reference cells for each player
rather than using a Vlookup function, and then you are using cell references
for the results, so sorting is not an option.
Without re-writing your whole sheet, you could do the following.

In cell M212 enter
=L212*100000+(100-G212) and copy down through L213:L234
This multiplies the points ofr that team manger by 100,000 and adds 100 -
his number in the table to the result. The reason for this is to resolve
problems when 2 managers have the same number of points, in which case they
will be listed in their original order within the table, Shawn Gates comes
above Doreen Goodyear, merely because he comes before her in the original
list.

Then in cell N21 enter
=L212*100000+(100-G212)and copy down through N213:N234
This sorts the range of values in L212:234 descending by points scored,
using the additional values as decribed above to resolve ties.

Choose InsertNameDefine and in the first pane type Points2 and in the
refers to pane type =$N$212:$N$234
Choose InsertNameDefine and in the first pane type Teams and in the refers
to pane type =$H212:$N234
Choose InsertNameDefine and in the first pane type Mangers and in the
refers to pane type =$H212:$H234

Copy your headings from H211:L211 to cell H237
In cell G238 enter =ROW()-237
In cell H238 enter =INDEX(Managers,MATCH(N212,points2,0))
In cell J238 enter =VLOOKUP($H238,Teams,3,0)
in cell L238 enter =VLOOKUP($H238,Teams,5,0)

Copy G238:L238 and paste through G239:G260

Copy range H211:L234 and go to cell H237 Paste SpecialFormats.

You can now hide columns M and N if you wish and the new table in G237:L260
will give your sorted list of results for each team.

Good luck and will Del Horno continue to score as well as the season
progresses???? My wife has him selected in her team, but I don't.

--

Roger Govier


"Whizz" wrote in message
...
Here you go:

http://www.megaupload.com/?d=27CUS14C

If you scroll down to H211 you'll see my league table but I haven't a clue
how to live update it so that when I update the players scores it updates
the
manager's league table.

Thanks for your help

"Bob Phillips" wrote:

Sample data might help, but it just sounds like SUMIF

=SUMIF(A1:A100,"Team A",B1:B100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Whizz" wrote in message
...
I'm running a fantasy football league at work and i'm updating it via
excel.
The system requires you to add points for each player in each team and
a
total is calculated. I would then like to create a league table to show
which
team is leading... it needs to update as I enter in the new points for
each
player. I know it's really simple but I can't for the life of me
remember
how
to do it.

Aplogies, I copied the came formula twice and had a typo in the cell address
(blame the wine!!!)
Make that, in cell N212 enter
=LARGE(points2,ROW(1:1))and copy down through N213:N234

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi Whizz

Not quite the way I have set up a Fantasy League table for my son (Arsenal
supporter), as you are having to enter the reference cells for each player
rather than using a Vlookup function, and then you are using cell
references for the results, so sorting is not an option.
Without re-writing your whole sheet, you could do the following.

In cell M212 enter
=L212*100000+(100-G212) and copy down through L213:L234
This multiplies the points ofr that team manger by 100,000 and adds 100 -
his number in the table to the result. The reason for this is to resolve
problems when 2 managers have the same number of points, in which case
they will be listed in their original order within the table, Shawn Gates
comes above Doreen Goodyear, merely because he comes before her in the
original list.

Then in cell N21 enter
=L212*100000+(100-G212)and copy down through N213:N234
This sorts the range of values in L212:234 descending by points scored,
using the additional values as decribed above to resolve ties.

Choose InsertNameDefine and in the first pane type Points2 and in the
refers to pane type =$N$212:$N$234
Choose InsertNameDefine and in the first pane type Teams and in the
refers to pane type =$H212:$N234
Choose InsertNameDefine and in the first pane type Mangers and in the
refers to pane type =$H212:$H234

Copy your headings from H211:L211 to cell H237
In cell G238 enter =ROW()-237
In cell H238 enter =INDEX(Managers,MATCH(N212,points2,0))
In cell J238 enter =VLOOKUP($H238,Teams,3,0)
in cell L238 enter =VLOOKUP($H238,Teams,5,0)

Copy G238:L238 and paste through G239:G260

Copy range H211:L234 and go to cell H237 Paste SpecialFormats.

You can now hide columns M and N if you wish and the new table in
G237:L260 will give your sorted list of results for each team.

Good luck and will Del Horno continue to score as well as the season
progresses???? My wife has him selected in her team, but I don't.

--

Roger Govier


"Whizz" wrote in message
...
Here you go:

http://www.megaupload.com/?d=27CUS14C

If you scroll down to H211 you'll see my league table but I haven't a
clue
how to live update it so that when I update the players scores it updates
the
manager's league table.

Thanks for your help

"Bob Phillips" wrote:

Sample data might help, but it just sounds like SUMIF

=SUMIF(A1:A100,"Team A",B1:B100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Whizz" wrote in message
...
I'm running a fantasy football league at work and i'm updating it via
excel.
The system requires you to add points for each player in each team and
a
total is calculated. I would then like to create a league table to
show
which
team is leading... it needs to update as I enter in the new points for
each
player. I know it's really simple but I can't for the life of me
remember
how
to do it.