Hi,
I have a worksheet with data in several rows. Column wise they consist of
names, points etc..

I am trying to setup a formula whereby the formula looks at the data in the
points columns and then re-orders the rows accordingly taking all the
information in the row with it, with the highest points total at the top and
lowest points total at the bottom.

Each points cell, has a formula in there already which pulls the points
total from other cells and combines them.
Hope you can help!
Thanks

sandmania,

Have your tried Data / Sort? Select the whole table, and the rows will stay together when you sort
based on one column.

If you want to create a second table that contains the same data as the first, but just sorted, then
it's a bit more complicated, but easily do-able.

Let us know....

HTH,
Bernie
MS Excel MVP


"sandmania" wrote in message
...
Hi,
I have a worksheet with data in several rows. Column wise they consist of
names, points etc..

I am trying to setup a formula whereby the formula looks at the data in the
points columns and then re-orders the rows accordingly taking all the
information in the row with it, with the highest points total at the top and
lowest points total at the bottom.

Each points cell, has a formula in there already which pulls the points
total from other cells and combines them.
Hope you can help!
Thanks

Forgive me if I'm oversimplyfing or not understanding what your asking for,
but couldn't you just use DataSort by Points column?
(Back up your data first, as you cannot undo a sort)
Good Luck...

"sandmania" wrote:

Hi,
I have a worksheet with data in several rows. Column wise they consist of
names, points etc..

I am trying to setup a formula whereby the formula looks at the data in the
points columns and then re-orders the rows accordingly taking all the
information in the row with it, with the highest points total at the top and
lowest points total at the bottom.

Each points cell, has a formula in there already which pulls the points
total from other cells and combines them.
Hope you can help!
Thanks

(Back up your data first, as you cannot undo a sort)

????

That would be news to many, if it were true....


Bernie

Hi,

Yeah I could use the sort by icon, but is there a way of doing it
automatically, so as soon as data is entered, it automatically calculates
which should go top etc....?

"Krissie C" wrote:

Forgive me if I'm oversimplyfing or not understanding what your asking for,
but couldn't you just use DataSort by Points column?
(Back up your data first, as you cannot undo a sort)
Good Luck...

"sandmania" wrote:

Hi,
I have a worksheet with data in several rows. Column wise they consist of
names, points etc..

I am trying to setup a formula whereby the formula looks at the data in the
points columns and then re-orders the rows accordingly taking all the
information in the row with it, with the highest points total at the top and
lowest points total at the bottom.

Each points cell, has a formula in there already which pulls the points
total from other cells and combines them.
Hope you can help!
Thanks

My bad...Peace

"Bernie Deitrick" wrote:


(Back up your data first, as you cannot undo a sort)

????

That would be news to many, if it were true....


Bernie

sand

You need a dynamic range for the points column and some worksheet code that
sorts when new data is entered.

See Debra Dalgleish's site for both these steps.

http://www.contextures.on.ca/xlNames01.html#Dynamic

Download her sample workbook "Update Multiple Validation Lists.zip"

http://www.contextures.on.ca/excelfiles.html

You are not working with DV Lists, but the same code can be used to auto-sort
your data when new data is entered.


Gord Dibben Excel MVP


On Mon, 19 Dec 2005 08:47:02 -0800, sandmania
wrote:

Hi,

Yeah I could use the sort by icon, but is there a way of doing it
automatically, so as soon as data is entered, it automatically calculates
which should go top etc....?

"Krissie C" wrote:

Forgive me if I'm oversimplyfing or not understanding what your asking for,
but couldn't you just use DataSort by Points column?
(Back up your data first, as you cannot undo a sort)
Good Luck...

"sandmania" wrote:

Hi,
I have a worksheet with data in several rows. Column wise they consist of
names, points etc..

I am trying to setup a formula whereby the formula looks at the data in the
points columns and then re-orders the rows accordingly taking all the
information in the row with it, with the highest points total at the top and
lowest points total at the bottom.

Each points cell, has a formula in there already which pulls the points
total from other cells and combines them.
Hope you can help!
Thanks

Hi,

Thanks for you reply.
Tried the below, and to some effect it does work, but again it doesn't shift
the entire row up or down.

An example of what i am trying
name played won points
bob 10 10 30
john 9 9 27
billy 10 8 24

say if billy gets 31 points, i want all the row data to move to the top.
similiarly the other rows will move either up/down when something changes.

thanks for those links though. They may come in useful

Kind Regards

"Gord Dibben" wrote:

sand

You need a dynamic range for the points column and some worksheet code that
sorts when new data is entered.

See Debra Dalgleish's site for both these steps.

http://www.contextures.on.ca/xlNames01.html#Dynamic

Download her sample workbook "Update Multiple Validation Lists.zip"

http://www.contextures.on.ca/excelfiles.html

You are not working with DV Lists, but the same code can be used to auto-sort
your data when new data is entered.


Gord Dibben Excel MVP


On Mon, 19 Dec 2005 08:47:02 -0800, sandmania
wrote:

Hi,

Yeah I could use the sort by icon, but is there a way of doing it
automatically, so as soon as data is entered, it automatically calculates
which should go top etc....?

"Krissie C" wrote:

Forgive me if I'm oversimplyfing or not understanding what your asking for,
but couldn't you just use DataSort by Points column?
(Back up your data first, as you cannot undo a sort)
Good Luck...

"sandmania" wrote:

Hi,
I have a worksheet with data in several rows. Column wise they consist of
names, points etc..

I am trying to setup a formula whereby the formula looks at the data in the
points columns and then re-orders the rows accordingly taking all the
information in the row with it, with the highest points total at the top and
lowest points total at the bottom.

Each points cell, has a formula in there already which pulls the points
total from other cells and combines them.
Hope you can help!
Thanks

If you have your original league table with 2 columns before, the first
used to rank the points + the row number/100 to allow for individuals
on joint points, then a second column ranking the first. Then in a new
table, where you have numbers down the left (for the number of players
in the league) and then put a vlookup for all of the columns in your
table. When you then alter the original tables points values. The new
table automatically recalculates.

See attached zipped spreadsheet


+-------------------------------------------------------------------+
|Filename: Example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4137 |
+-------------------------------------------------------------------+

--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=494590

Hi,

Thanks for that info...I will give that a go. The link you specified below
doesnt seem to work.

kind regards

"Gary Brown" wrote:


If you have your original league table with 2 columns before, the first
used to rank the points + the row number/100 to allow for individuals
on joint points, then a second column ranking the first. Then in a new
table, where you have numbers down the left (for the number of players
in the league) and then put a vlookup for all of the columns in your
table. When you then alter the original tables points values. The new
table automatically recalculates.

See attached zipped spreadsheet


+-------------------------------------------------------------------+
|Filename: Example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4137 |
+-------------------------------------------------------------------+

--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=494590

If you need the example, let me know and I will email it to you


--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=494590

Hi,

An example would be superb. my email address is

Thankyou very much

Kind Regards


"Gary Brown" wrote:


If you need the example, let me know and I will email it to you


--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=494590

correction to the below. my email is .

thanks

"sandmania" wrote:

Hi,

An example would be superb. my email address is

Thankyou very much

Kind Regards


"Gary Brown" wrote:


If you need the example, let me know and I will email it to you


--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=494590

Thats absolutely brilliant....just what I was trying to do. Much appreciated.

thankyou
Kind Regards

"Gary Brown" wrote:


If you need the example, let me know and I will email it to you


--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=494590