Football Tourament Spreadsheet - Sorting a table automatically.

[Hardip]

Hello

I've offered to do a spreadheet for a charity football tourament. There are
16 teams, 4 tables. I've done the following:

1. Created a schedule of games for all teams were the score is populated
2. This then updates the tables based on a win (3 points), a draw (1 point),
a loss (0 points).

It's working well but I spotted that the table is static. As the four teams
accumlate points over the games (e.g. in Table A), the team with the most
points at that stage should be at the top of the table. This changeable and
therefore I need a method to dynamically sort the table as the results are
inputed.

At present the third team in Table A stays third regardless of the points.
I therefore need a dynamic method to sort the table on points as each teams
score is populated. I could do this manually, but I'll be playing (badly)
and also helping elsewhere. The score keeper has no spreadsheet skills and is
just about OK with populating the individual scores - but I don't want her to
keep sorting because she might make an unforeseen error.

Can the 'sort' be done dynamically? I hope that made sense!

I'd appreciate any thoughts or guidance.

Thanks to all who reply,
Hardip

[Bob Phillips]

The 'usual' way is to have a hidden table of vanilla results, and then have
a display table to show the sorted results, The hidden table would have a
rank column, and the display table would just lookup the associated values
corresponding to the ranks,

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Hardip" <Hardip@discussions.microsoft.com> wrote in message
news:D5C0B2E2-731B-4695-8636-474B86F7C02B@microsoft.com...
> Hello
>
> I've offered to do a spreadheet for a charity football tourament. There
are
> 16 teams, 4 tables. I've done the following:
>
> 1. Created a schedule of games for all teams were the score is populated
> 2. This then updates the tables based on a win (3 points), a draw (1
point),
> a loss (0 points).
>
> It's working well but I spotted that the table is static. As the four
teams
> accumlate points over the games (e.g. in Table A), the team with the most
> points at that stage should be at the top of the table. This changeable
and
> therefore I need a method to dynamically sort the table as the results are
> inputed.
>
> At present the third team in Table A stays third regardless of the points.
> I therefore need a dynamic method to sort the table on points as each
teams
> score is populated. I could do this manually, but I'll be playing (badly)
> and also helping elsewhere. The score keeper has no spreadsheet skills and
is
> just about OK with populating the individual scores - but I don't want her
to
> keep sorting because she might make an unforeseen error.
>
> Can the 'sort' be done dynamically? I hope that made sense!
>
> I'd appreciate any thoughts or guidance.
>
> Thanks to all who reply,
> Hardip

[Hardip]

Hi Bob

Thanks for responding. Can you please clarify what you mean as it sounds
like something I can do with a little more information.

Many (many) thanks,
Hardip

"Bob Phillips" wrote:

> The 'usual' way is to have a hidden table of vanilla results, and then have
> a display table to show the sorted results, The hidden table would have a
> rank column, and the display table would just lookup the associated values
> corresponding to the ranks,
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Hardip" <Hardip@discussions.microsoft.com> wrote in message
> news:D5C0B2E2-731B-4695-8636-474B86F7C02B@microsoft.com...
> > Hello
> >
> > I've offered to do a spreadheet for a charity football tourament. There
> are
> > 16 teams, 4 tables. I've done the following:
> >
> > 1. Created a schedule of games for all teams were the score is populated
> > 2. This then updates the tables based on a win (3 points), a draw (1
> point),
> > a loss (0 points).
> >
> > It's working well but I spotted that the table is static. As the four
> teams
> > accumlate points over the games (e.g. in Table A), the team with the most
> > points at that stage should be at the top of the table. This changeable
> and
> > therefore I need a method to dynamically sort the table as the results are
> > inputed.
> >
> > At present the third team in Table A stays third regardless of the points.
> > I therefore need a dynamic method to sort the table on points as each
> teams
> > score is populated. I could do this manually, but I'll be playing (badly)
> > and also helping elsewhere. The score keeper has no spreadsheet skills and
> is
> > just about OK with populating the individual scores - but I don't want her
> to
> > keep sorting because she might make an unforeseen error.
> >
> > Can the 'sort' be done dynamically? I hope that made sense!
> >
> > I'd appreciate any thoughts or guidance.
> >
> > Thanks to all who reply,
> > Hardip
>
>
>

[Bob Phillips]

Give me a half-hour, and I will post an example.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Hardip" <Hardip@discussions.microsoft.com> wrote in message
news:8287A68F-4938-4618-85C9-B9C24069FFC0@microsoft.com...
> Hi Bob
>
> Thanks for responding. Can you please clarify what you mean as it sounds
> like something I can do with a little more information.
>
> Many (many) thanks,
> Hardip
>
> "Bob Phillips" wrote:
>
> > The 'usual' way is to have a hidden table of vanilla results, and then
have
> > a display table to show the sorted results, The hidden table would have
a
> > rank column, and the display table would just lookup the associated
values
> > corresponding to the ranks,
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Hardip" <Hardip@discussions.microsoft.com> wrote in message
> > news:D5C0B2E2-731B-4695-8636-474B86F7C02B@microsoft.com...
> > > Hello
> > >
> > > I've offered to do a spreadheet for a charity football tourament.
There
> > are
> > > 16 teams, 4 tables. I've done the following:
> > >
> > > 1. Created a schedule of games for all teams were the score is
populated
> > > 2. This then updates the tables based on a win (3 points), a draw (1
> > point),
> > > a loss (0 points).
> > >
> > > It's working well but I spotted that the table is static. As the four
> > teams
> > > accumlate points over the games (e.g. in Table A), the team with the
most
> > > points at that stage should be at the top of the table. This
changeable
> > and
> > > therefore I need a method to dynamically sort the table as the results
are
> > > inputed.
> > >
> > > At present the third team in Table A stays third regardless of the
points.
> > > I therefore need a dynamic method to sort the table on points as each
> > teams
> > > score is populated. I could do this manually, but I'll be playing
(badly)
> > > and also helping elsewhere. The score keeper has no spreadsheet skills
and
> > is
> > > just about OK with populating the individual scores - but I don't want
her
> > to
> > > keep sorting because she might make an unforeseen error.
> > >
> > > Can the 'sort' be done dynamically? I hope that made sense!
> > >
> > > I'd appreciate any thoughts or guidance.
> > >
> > > Thanks to all who reply,
> > > Hardip
> >
> >
> >

[excelnewbieian]

Bob has just helped me out doing something very similar...

My finished version of the English Football Conference Table for the forthcoming season is at http://www.freefilehosting.org/publi...ce%20Table.xls

The table I will use is the one in the top left hand side. By the way I have to enter each score twice... mostley because that was the only way I knew yesturday before I started researching into excel!

If this is of any use to use to you please feel free to use it!

Ian.

[Bob Phillips]

Here is an example of what I mean http://cjoint.com/?ilqRIPTZ41

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" <bob.NGs@somewhere.com> wrote in message
news:ueYbV%23UvGHA.4460@TK2MSFTNGP05.phx.gbl...
> Give me a half-hour, and I will post an example.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Hardip" <Hardip@discussions.microsoft.com> wrote in message
> news:8287A68F-4938-4618-85C9-B9C24069FFC0@microsoft.com...
> > Hi Bob
> >
> > Thanks for responding. Can you please clarify what you mean as it
sounds
> > like something I can do with a little more information.
> >
> > Many (many) thanks,
> > Hardip
> >
> > "Bob Phillips" wrote:
> >
> > > The 'usual' way is to have a hidden table of vanilla results, and then
> have
> > > a display table to show the sorted results, The hidden table would
have
> a
> > > rank column, and the display table would just lookup the associated
> values
> > > corresponding to the ranks,
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "Hardip" <Hardip@discussions.microsoft.com> wrote in message
> > > news:D5C0B2E2-731B-4695-8636-474B86F7C02B@microsoft.com...
> > > > Hello
> > > >
> > > > I've offered to do a spreadheet for a charity football tourament.
> There
> > > are
> > > > 16 teams, 4 tables. I've done the following:
> > > >
> > > > 1. Created a schedule of games for all teams were the score is
> populated
> > > > 2. This then updates the tables based on a win (3 points), a draw (1
> > > point),
> > > > a loss (0 points).
> > > >
> > > > It's working well but I spotted that the table is static. As the
four
> > > teams
> > > > accumlate points over the games (e.g. in Table A), the team with the
> most
> > > > points at that stage should be at the top of the table. This
> changeable
> > > and
> > > > therefore I need a method to dynamically sort the table as the
results
> are
> > > > inputed.
> > > >
> > > > At present the third team in Table A stays third regardless of the
> points.
> > > > I therefore need a dynamic method to sort the table on points as
each
> > > teams
> > > > score is populated. I could do this manually, but I'll be playing
> (badly)
> > > > and also helping elsewhere. The score keeper has no spreadsheet
skills
> and
> > > is
> > > > just about OK with populating the individual scores - but I don't
want
> her
> > > to
> > > > keep sorting because she might make an unforeseen error.
> > > >
> > > > Can the 'sort' be done dynamically? I hope that made sense!
> > > >
> > > > I'd appreciate any thoughts or guidance.
> > > >
> > > > Thanks to all who reply,
> > > > Hardip
> > >
> > >
> > >
>
>

[Bob Phillips]

Ian,

the link is coming up as invalid (wanted to see your finished efforts :-))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"excelnewbieian"
<excelnewbieian.2cdpz3_1155305705.982@excelforum-nospam.com> wrote in
message news:excelnewbieian.2cdpz3_1155305705.982@excelforum-nospam.com...
>
> Bob has just helped me out doing something very similar...
>
> My finished version of the English Football Conference Table for the
> forthcuming season is at
> http://www.unofficiallyfleet.com/Conference%20Table.xls.
>
> The table I will use is the one in the top left hand side. By the way I
> have to enter each score twice... mostley because that was the only way
> I knew yesturday before I started researching into excel!
>
> If this is of any use to use to you please feel free to use it!
>
> Ian.
>
>
> --
> excelnewbieian
> ------------------------------------------------------------------------
> excelnewbieian's Profile:
http://www.excelforum.com/member.php...o&userid=37380
> View this thread: http://www.excelforum.com/showthread...hreadid=570695
>

[excelnewbieian]

Its working now :D

I had to sign up to a free service that hosts xls because the one I paid for to host a website on didnt let me... !

Anyhow let me know what you think... and thanx 4 ur help again!

Heres the file incase you missed the other link...
http://www.freefilehosting.org/publi...ce%20Table.xls

[Pete_UK]

Are you an Arsenal supporter, then, Bob? <g>

Pete

Bob Phillips wrote:
> Here is an example of what I mean http://cjoint.com/?ilqRIPTZ41
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Bob Phillips" <bob.NGs@somewhere.com> wrote in message
> news:ueYbV%23UvGHA.4460@TK2MSFTNGP05.phx.gbl...
> > Give me a half-hour, and I will post an example.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Hardip" <Hardip@discussions.microsoft.com> wrote in message
> > news:8287A68F-4938-4618-85C9-B9C24069FFC0@microsoft.com...
> > > Hi Bob
> > >
> > > Thanks for responding. Can you please clarify what you mean as it
> sounds
> > > like something I can do with a little more information.
> > >
> > > Many (many) thanks,
> > > Hardip
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > The 'usual' way is to have a hidden table of vanilla results, and then
> > have
> > > > a display table to show the sorted results, The hidden table would
> have
> > a
> > > > rank column, and the display table would just lookup the associated
> > values
> > > > corresponding to the ranks,
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > "Hardip" <Hardip@discussions.microsoft.com> wrote in message
> > > > news:D5C0B2E2-731B-4695-8636-474B86F7C02B@microsoft.com...
> > > > > Hello
> > > > >
> > > > > I've offered to do a spreadheet for a charity football tourament.
> > There
> > > > are
> > > > > 16 teams, 4 tables. I've done the following:
> > > > >
> > > > > 1. Created a schedule of games for all teams were the score is
> > populated
> > > > > 2. This then updates the tables based on a win (3 points), a draw (1
> > > > point),
> > > > > a loss (0 points).
> > > > >
> > > > > It's working well but I spotted that the table is static. As the
> four
> > > > teams
> > > > > accumlate points over the games (e.g. in Table A), the team with the
> > most
> > > > > points at that stage should be at the top of the table. This
> > changeable
> > > > and
> > > > > therefore I need a method to dynamically sort the table as the
> results
> > are
> > > > > inputed.
> > > > >
> > > > > At present the third team in Table A stays third regardless of the
> > points.
> > > > > I therefore need a dynamic method to sort the table on points as
> each
> > > > teams
> > > > > score is populated. I could do this manually, but I'll be playing
> > (badly)
> > > > > and also helping elsewhere. The score keeper has no spreadsheet
> skills
> > and
> > > > is
> > > > > just about OK with populating the individual scores - but I don't
> want
> > her
> > > > to
> > > > > keep sorting because she might make an unforeseen error.
> > > > >
> > > > > Can the 'sort' be done dynamically? I hope that made sense!
> > > > >
> > > > > I'd appreciate any thoughts or guidance.
> > > > >
> > > > > Thanks to all who reply,
> > > > > Hardip
> > > >
> > > >
> > > >
> >
> >

[Bob Phillips]

Well I'm certainly not a Chelsea fan <G>

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Pete_UK" <pashurst@auditel.net> wrote in message
news:1155308413.711141.198150@m73g2000cwd.googlegroups.com...
> Are you an Arsenal supporter, then, Bob? <g>
>
> Pete
>
> Bob Phillips wrote:
> > Here is an example of what I mean http://cjoint.com/?ilqRIPTZ41
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Bob Phillips" <bob.NGs@somewhere.com> wrote in message
> > news:ueYbV%23UvGHA.4460@TK2MSFTNGP05.phx.gbl...
> > > Give me a half-hour, and I will post an example.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "Hardip" <Hardip@discussions.microsoft.com> wrote in message
> > > news:8287A68F-4938-4618-85C9-B9C24069FFC0@microsoft.com...
> > > > Hi Bob
> > > >
> > > > Thanks for responding. Can you please clarify what you mean as it
> > sounds
> > > > like something I can do with a little more information.
> > > >
> > > > Many (many) thanks,
> > > > Hardip
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > The 'usual' way is to have a hidden table of vanilla results, and
then
> > > have
> > > > > a display table to show the sorted results, The hidden table would
> > have
> > > a
> > > > > rank column, and the display table would just lookup the
associated
> > > values
> > > > > corresponding to the ranks,
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (replace somewhere in email address with gmail if mailing direct)
> > > > >
> > > > > "Hardip" <Hardip@discussions.microsoft.com> wrote in message
> > > > > news:D5C0B2E2-731B-4695-8636-474B86F7C02B@microsoft.com...
> > > > > > Hello
> > > > > >
> > > > > > I've offered to do a spreadheet for a charity football
tourament.
> > > There
> > > > > are
> > > > > > 16 teams, 4 tables. I've done the following:
> > > > > >
> > > > > > 1. Created a schedule of games for all teams were the score is
> > > populated
> > > > > > 2. This then updates the tables based on a win (3 points), a
draw (1
> > > > > point),
> > > > > > a loss (0 points).
> > > > > >
> > > > > > It's working well but I spotted that the table is static. As
the
> > four
> > > > > teams
> > > > > > accumlate points over the games (e.g. in Table A), the team with
the
> > > most
> > > > > > points at that stage should be at the top of the table. This
> > > changeable
> > > > > and
> > > > > > therefore I need a method to dynamically sort the table as the
> > results
> > > are
> > > > > > inputed.
> > > > > >
> > > > > > At present the third team in Table A stays third regardless of
the
> > > points.
> > > > > > I therefore need a dynamic method to sort the table on points as
> > each
> > > > > teams
> > > > > > score is populated. I could do this manually, but I'll be
playing
> > > (badly)
> > > > > > and also helping elsewhere. The score keeper has no spreadsheet
> > skills
> > > and
> > > > > is
> > > > > > just about OK with populating the individual scores - but I
don't
> > want
> > > her
> > > > > to
> > > > > > keep sorting because she might make an unforeseen error.
> > > > > >
> > > > > > Can the 'sort' be done dynamically? I hope that made sense!
> > > > > >
> > > > > > I'd appreciate any thoughts or guidance.
> > > > > >
> > > > > > Thanks to all who reply,
> > > > > > Hardip
> > > > >
> > > > >
> > > > >
> > >
> > >
>

[Bob Phillips]

I'm not quite getting how the scores get entered. I put Aldershot 1,
Altrincham 2 twice, and it showed against Aldershot as 2 games, none against
Altrincham.

BTW, have you seen this http://xldynamic.com/source/xld.LeagueTable.html

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"excelnewbieian"
<excelnewbieian.2cds22_1155308405.6841@excelforum-nospam.com> wrote in
message news:excelnewbieian.2cds22_1155308405.6841@excelforum-nospam.com...
>
> Its working now :D
>
> I had to sign up to a free service that hosts xls because the one I
> paid for and host a website on didnt let me...
>
> Anyhow let me know what you think... and thanx 4 ur help again!
>
> Heres the file incase you missed the other link...
> http://www.freefilehosting.org/publi...ce%20Table.xls
>
>
> --
> excelnewbieian
> ------------------------------------------------------------------------
> excelnewbieian's Profile:
http://www.excelforum.com/member.php...o&userid=37380
> View this thread: http://www.excelforum.com/showthread...hreadid=570695
>

[excelnewbieian]

Looks like a very interesting article, but its very late atm so will have a look in the morning....


As for my spreadsheet the only way I knew at the time (yesturday (!)) was to do it the way I have, i.e entering scores twice...if you see what I mean...

Will report back in the morning... or after the weekend away....