Who can help please?
I am running a football league for a group of friends. I have a table which
shows how many points each one has accumulated. I use the "range" function to
show which position each person has in the league. What I would like to do is
to have on a separate table the same list but this time I would like it to
show their names just like a normal league table, ie. most points at top,
least at bottom.
I have 3 columns, Column A has a list of names, Column B shows their
position and Column C has their points. At the moment we are 30 people.
Can someone help to make a proper table.
Hope so.
Best regards
Soz

You can use an event listener which checks for a change of points and sorts
the second list if so.
A simple sample is he
http://www.geocities.com/smplprgrsrc...all_League.zip




"Soz" schreef in bericht
...
Who can help please?
I am running a football league for a group of friends. I have a table
which
shows how many points each one has accumulated. I use the "range" function
to
show which position each person has in the league. What I would like to do
is
to have on a separate table the same list but this time I would like it to
show their names just like a normal league table, ie. most points at top,
least at bottom.
I have 3 columns, Column A has a list of names, Column B shows their
position and Column C has their points. At the moment we are 30 people.
Can someone help to make a proper table.
Hope so.
Best regards
Soz

Hello and many thanks for your help.
I see how it works but would it be possible to tell me how to set up the
macro so I can incorporate it into the spreadsheet which I am currently using.
Again many thanks
Soz

"moi" schrieb:

You can use an event listener which checks for a change of points and sorts
the second list if so.
A simple sample is he
http://www.geocities.com/smplprgrsrc...all_League.zip




"Soz" schreef in bericht
...
Who can help please?
I am running a football league for a group of friends. I have a table
which
shows how many points each one has accumulated. I use the "range" function
to
show which position each person has in the league. What I would like to do
is
to have on a separate table the same list but this time I would like it to
show their names just like a normal league table, ie. most points at top,
least at bottom.
I have 3 columns, Column A has a list of names, Column B shows their
position and Column C has their points. At the moment we are 30 people.
Can someone help to make a proper table.
Hope so.
Best regards
Soz

The macro has 3 parts:
- a class module, there an event handler is declared
- a module, this one has an auto_opener, to activate/install the event
handler
- a piece of code in Sheet1, which actally is the event handler that
triggers any change in the workbook
this last one calls a sort macro which needs to be put in a 'normal module',
not in a class

Just check out help for 'events' and you'll see that you can do a lot more,
BeforeSave, BeforeClose, ActivateSheet - those are all events that you can
make Excel respond to in your own way.


"Soz" schreef in bericht
...
Hello and many thanks for your help.
I see how it works but would it be possible to tell me how to set up the
macro so I can incorporate it into the spreadsheet which I am currently
using.
Again many thanks
Soz

"moi" schrieb:

You can use an event listener which checks for a change of points and
sorts
the second list if so.
A simple sample is he
http://www.geocities.com/smplprgrsrc...all_League.zip




"Soz" schreef in bericht
...
Who can help please?
I am running a football league for a group of friends. I have a table
which
shows how many points each one has accumulated. I use the "range"
function
to
show which position each person has in the league. What I would like to
do
is
to have on a separate table the same list but this time I would like it
to
show their names just like a normal league table, ie. most points at
top,
least at bottom.
I have 3 columns, Column A has a list of names, Column B shows their
position and Column C has their points. At the moment we are 30 people.
Can someone help to make a proper table.
Hope so.
Best regards
Soz

Hello and thanks for the quick reply.
Sounds complicated for me but I will definitely try it. I have to sort it
out by next week as have to send everything out then. Thanks again.
Soz

"moi" schrieb:

The macro has 3 parts:
- a class module, there an event handler is declared
- a module, this one has an auto_opener, to activate/install the event
handler
- a piece of code in Sheet1, which actally is the event handler that
triggers any change in the workbook
this last one calls a sort macro which needs to be put in a 'normal module',
not in a class

Just check out help for 'events' and you'll see that you can do a lot more,
BeforeSave, BeforeClose, ActivateSheet - those are all events that you can
make Excel respond to in your own way.


"Soz" schreef in bericht
...
Hello and many thanks for your help.
I see how it works but would it be possible to tell me how to set up the
macro so I can incorporate it into the spreadsheet which I am currently
using.
Again many thanks
Soz

"moi" schrieb:

You can use an event listener which checks for a change of points and
sorts
the second list if so.
A simple sample is he
http://www.geocities.com/smplprgrsrc...all_League.zip




"Soz" schreef in bericht
...
Who can help please?
I am running a football league for a group of friends. I have a table
which
shows how many points each one has accumulated. I use the "range"
function
to
show which position each person has in the league. What I would like to
do
is
to have on a separate table the same list but this time I would like it
to
show their names just like a normal league table, ie. most points at
top,
least at bottom.
I have 3 columns, Column A has a list of names, Column B shows their
position and Column C has their points. At the moment we are 30 people.
Can someone help to make a proper table.
Hope so.
Best regards
Soz

Hi

Whilst the solution presented by "moi" is elegant and works automatically,
if you are concerned about being able to achieve the necessary steps you
could carry out your task in a simplified way by running a macro to do the
sorting as and when you wished.

In your second sheet, your sorted list, you say that column B is your
ranking.
In cell B2 enter
=ROW()-1
copy down through B3:B31

Now, switch on the macro recorder ToolsMacroRecord before you select the
range A1:C31, then DataSort select Column C as the sort column and choose
Descending. Then switch off the macro recorder.

Whenever you want to produce your sorted list, just run the macro.

--
Regards
Roger Govier
"Soz" wrote in message
...
Hello and thanks for the quick reply.
Sounds complicated for me but I will definitely try it. I have to sort it
out by next week as have to send everything out then. Thanks again.
Soz

"moi" schrieb:

The macro has 3 parts:
- a class module, there an event handler is declared
- a module, this one has an auto_opener, to activate/install the event
handler
- a piece of code in Sheet1, which actally is the event handler that
triggers any change in the workbook
this last one calls a sort macro which needs to be put in a 'normal
module',
not in a class

Just check out help for 'events' and you'll see that you can do a lot
more,
BeforeSave, BeforeClose, ActivateSheet - those are all events that you
can
make Excel respond to in your own way.


"Soz" schreef in bericht
...
Hello and many thanks for your help.
I see how it works but would it be possible to tell me how to set up
the
macro so I can incorporate it into the spreadsheet which I am currently
using.
Again many thanks
Soz

"moi" schrieb:

You can use an event listener which checks for a change of points and
sorts
the second list if so.
A simple sample is he
http://www.geocities.com/smplprgrsrc...all_League.zip




"Soz" schreef in bericht
...
Who can help please?
I am running a football league for a group of friends. I have a
table
which
shows how many points each one has accumulated. I use the "range"
function
to
show which position each person has in the league. What I would like
to
do
is
to have on a separate table the same list but this time I would like
it
to
show their names just like a normal league table, ie. most points at
top,
least at bottom.
I have 3 columns, Column A has a list of names, Column B shows their
position and Column C has their points. At the moment we are 30
people.
Can someone help to make a proper table.
Hope so.
Best regards
Soz

This is if you do not want to use a macro.
With your information for the 30 players in cells A1 to A30, enter the
following.
In cell D1:
=C1+ROW(C1)/1000
In cell E1:
=RANK($D1,$D$1:$D$30)
In cell F1:
=ROW(A1)
In cell G1:
=A1
In cell H1:
=VLOOKUP($K1,$F$1:$G$30,2)
In cell I1:
=SUMPRODUCT($B$1:$B$30,--($F$1:$F$30=$K1))
In cell J1:
=SUMPRODUCT($C$1:$C$30,--($F$1:$F$30=K1))
In cell K1:
=SUMPRODUCT($F$1:$F$30,--($E$1:$E$30=F1))
Copy down.
Range H1:J30 gives the information in the order you need.

"Soz" wrote:

Who can help please?
I am running a football league for a group of friends. I have a table which
shows how many points each one has accumulated. I use the "range" function to
show which position each person has in the league. What I would like to do is
to have on a separate table the same list but this time I would like it to
show their names just like a normal league table, ie. most points at top,
least at bottom.
I have 3 columns, Column A has a list of names, Column B shows their
position and Column C has their points. At the moment we are 30 people.
Can someone help to make a proper table.
Hope so.
Best regards
Soz