software

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!
Thankssandmania,

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 MVPquot;sandmaniaquot; gt; wrote in message
...
gt; Hi,
gt; I have a worksheet with data in several rows. Column wise they consist of
gt; names, points etc..
gt;
gt; I am trying to setup a formula whereby the formula looks at the data in the
gt; points columns and then re-orders the rows accordingly taking all the
gt; information in the row with it, with the highest points total at the top and
gt; lowest points total at the bottom.
gt;
gt; Each points cell, has a formula in there already which pulls the points
gt; total from other cells and combines them.
gt; Hope you can help!
gt; Thanks
gt;
Forgive me if I'm oversimplyfing or not understanding what your asking for,
but couldn't you just use Datagt;Sort by Points column?
(Back up your data first, as you cannot undo a sort)
Good Luck...

quot;sandmaniaquot; wrote:

gt; Hi,
gt; I have a worksheet with data in several rows. Column wise they consist of
gt; names, points etc..
gt;
gt; I am trying to setup a formula whereby the formula looks at the data in the
gt; points columns and then re-orders the rows accordingly taking all the
gt; information in the row with it, with the highest points total at the top and
gt; lowest points total at the bottom.
gt;
gt; Each points cell, has a formula in there already which pulls the points
gt; total from other cells and combines them.
gt; Hope you can help!
gt; Thanks
gt;


gt; (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....?

quot;Krissie Cquot; wrote:

gt; Forgive me if I'm oversimplyfing or not understanding what your asking for,
gt; but couldn't you just use Datagt;Sort by Points column?
gt; (Back up your data first, as you cannot undo a sort)
gt; Good Luck...
gt;
gt; quot;sandmaniaquot; wrote:
gt;
gt; gt; Hi,
gt; gt; I have a worksheet with data in several rows. Column wise they consist of
gt; gt; names, points etc..
gt; gt;
gt; gt; I am trying to setup a formula whereby the formula looks at the data in the
gt; gt; points columns and then re-orders the rows accordingly taking all the
gt; gt; information in the row with it, with the highest points total at the top and
gt; gt; lowest points total at the bottom.
gt; gt;
gt; gt; Each points cell, has a formula in there already which pulls the points
gt; gt; total from other cells and combines them.
gt; gt; Hope you can help!
gt; gt; Thanks
gt; gt;

My bad...Peace

quot;Bernie Deitrickquot; wrote:

gt;
gt; gt; (Back up your data first, as you cannot undo a sort)
gt;
gt; ????
gt;
gt; That would be news to many, if it were true....
gt;
gt;
gt; Bernie
gt;
gt;
gt;

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.

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

Download her sample workbook quot;Update Multiple Validation Lists.zipquot;

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 MVPOn Mon, 19 Dec 2005 08:47:02 -0800, sandmania
gt; wrote:

gt;Hi,
gt;
gt;Yeah I could use the sort by icon, but is there a way of doing it
gt;automatically, so as soon as data is entered, it automatically calculates
gt;which should go top etc....?
gt;
gt;quot;Krissie Cquot; wrote:
gt;
gt;gt; Forgive me if I'm oversimplyfing or not understanding what your asking for,
gt;gt; but couldn't you just use Datagt;Sort by Points column?
gt;gt; (Back up your data first, as you cannot undo a sort)
gt;gt; Good Luck...
gt;gt;
gt;gt; quot;sandmaniaquot; wrote:
gt;gt;
gt;gt; gt; Hi,
gt;gt; gt; I have a worksheet with data in several rows. Column wise they consist of
gt;gt; gt; names, points etc..
gt;gt; gt;
gt;gt; gt; I am trying to setup a formula whereby the formula looks at the data in the
gt;gt; gt; points columns and then re-orders the rows accordingly taking all the
gt;gt; gt; information in the row with it, with the highest points total at the top and
gt;gt; gt; lowest points total at the bottom.
gt;gt; gt;
gt;gt; gt; Each points cell, has a formula in there already which pulls the points
gt;gt; gt; total from other cells and combines them.
gt;gt; gt; Hope you can help!
gt;gt; gt; Thanks
gt;gt; gt;

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

quot;Gord Dibbenquot; wrote:

gt; sand
gt;
gt; You need a dynamic range for the points column and some worksheet code that
gt; sorts when new data is entered.
gt;
gt; See Debra Dalgleish's site for both these steps.
gt;
gt; www.contextures.on.ca/xlNames01.html#Dynamic
gt;
gt; Download her sample workbook quot;Update Multiple Validation Lists.zipquot;
gt;
gt; www.contextures.on.ca/excelfiles.html
gt;
gt; You are not working with DV Lists, but the same code can be used to auto-sort
gt; your data when new data is entered.
gt;
gt;
gt; Gord Dibben Excel MVP
gt;
gt;
gt; On Mon, 19 Dec 2005 08:47:02 -0800, sandmania
gt; gt; wrote:
gt;
gt; gt;Hi,
gt; gt;
gt; gt;Yeah I could use the sort by icon, but is there a way of doing it
gt; gt;automatically, so as soon as data is entered, it automatically calculates
gt; gt;which should go top etc....?
gt; gt;
gt; gt;quot;Krissie Cquot; wrote:
gt; gt;
gt; gt;gt; Forgive me if I'm oversimplyfing or not understanding what your asking for,
gt; gt;gt; but couldn't you just use Datagt;Sort by Points column?
gt; gt;gt; (Back up your data first, as you cannot undo a sort)
gt; gt;gt; Good Luck...
gt; gt;gt;
gt; gt;gt; quot;sandmaniaquot; wrote:
gt; gt;gt;
gt; gt;gt; gt; Hi,
gt; gt;gt; gt; I have a worksheet with data in several rows. Column wise they consist of
gt; gt;gt; gt; names, points etc..
gt; gt;gt; gt;
gt; gt;gt; gt; I am trying to setup a formula whereby the formula looks at the data in the
gt; gt;gt; gt; points columns and then re-orders the rows accordingly taking all the
gt; gt;gt; gt; information in the row with it, with the highest points total at the top and
gt; gt;gt; gt; lowest points total at the bottom.
gt; gt;gt; gt;
gt; gt;gt; gt; Each points cell, has a formula in there already which pulls the points
gt; gt;gt; gt; total from other cells and combines them.
gt; gt;gt; gt; Hope you can help!
gt; gt;gt; gt; Thanks
gt; gt;gt; gt;
gt;


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: www.excelforum.com/attachment.php?postid=4137 |
-------------------------------------------------------------------

--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: www.excelforum.com/member.php...oamp;userid=17084
View this thread: www.excelforum.com/showthread...hreadid=494590Hi,

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

kind regards

quot;Gary Brownquot; wrote:

gt;
gt; If you have your original league table with 2 columns before, the first
gt; used to rank the points the row number/100 to allow for individuals
gt; on joint points, then a second column ranking the first. Then in a new
gt; table, where you have numbers down the left (for the number of players
gt; in the league) and then put a vlookup for all of the columns in your
gt; table. When you then alter the original tables points values. The new
gt; table automatically recalculates.
gt;
gt; See attached zipped spreadsheet
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: Example.zip |
gt; |Download: www.excelforum.com/attachment.php?postid=4137 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; Gary Brown
gt; ------------------------------------------------------------------------
gt; Gary Brown's Profile: www.excelforum.com/member.php...oamp;userid=17084
gt; View this thread: www.excelforum.com/showthread...hreadid=494590
gt;
gt;