Creating a league in excel

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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 Insert>Name>Define and in the first pane type Points2 and in the
refers to pane type =$N$212:$N$234
Choose Insert>Name>Define and in the first pane type Teams and in the refers
to pane type =$H212:$N234
Choose Insert>Name>Define 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 Special>Formats.

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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 Insert>Name>Define and in the first pane type Points2 and in the
> refers to pane type =$N$212:$N$234
> Choose Insert>Name>Define and in the first pane type Teams and in the
> refers to pane type =$H212:$N234
> Choose Insert>Name>Define 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 Special>Formats.
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > 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.
>>>
>>>
>>>
>
>