Re: Auto sort in real time

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: RagDyer (ragdyer_at_cutoutmsn.com)
Date: 04/07/04 

Date: Tue, 6 Apr 2004 20:04:27 -0700

Even though we've been talking about sorting all along, the cells containing
the formula are *not* actually being sorted.
They're just displaying the contents of column A in the rank of the values
contained, equal to the row number that they (the formulas) are entered
into.
AFAIK, this can't be accomplished with text. 

-- 
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------
"Bernard Jordan >" <<Bernard.Jordan.14bg6z@excelforum-nospam.com> wrote in
message news:Bernard.Jordan.14bg6z@excelforum-nospam.com...
RagDyer wrote:
> *If by data, you mean numbers, or formulas that yield numbers, this
> can be
> done.
>
> However, since you stipulated;
> <<"without using VBA">>,
> a formula must be used, and since a formula is used, you cannot enter
> data
> into the formula cell itself to be sorted.
> This means utilizing a "helper" column, perhaps right along side the
> actual
> values column, where the sorting will take place and be displayed.
> The original column of values will remain unchanged.
>
> With data in A1:A50,
>
> Enter this in B1 to sort *ascending*:
> =SMALL($A$1:$A$50,ROW())
>
> OR
>
> Enter this in B1 to sort *descending*:
> =LARGE($A$1:$A$50,ROW())
>
> Adjust ranges as necessary and drag down to copy, or, if data is
> already
> entered in column A, just double click the "fill handle".
> --
>
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "Bernard Jordan >" <<Bernard.Jordan.149env@excelforum-nospam.com>
> wrote in
> message news:Bernard.Jordan.149env@excelforum-nospam.com...
> Is it possible to auto sort data so that changes update themselves
> in
> real time without using VBA.
> I have sorted a column of data in decending order but when I change
> a
> value in that column it does not update its self and re-arrange the
> order of values automatically. Can I make it do that for me? I know
> how
> to create a macro that will re-apply the sort but a realtime
> re-arrangement of data (like in Access) would be much better. Can
> anyone help me?
>
> Many thanks
> Bernard Jordan - Liverpool UK
>
>
> ---
> Message posted from http://www.ExcelForum.com/ *  Thanks for your help,
however I still need more help:
Rag Dyer - Your formula worked a treat but how do I get the information
in the column(s) next to the data that changes to stay in sequence, eg.
If I had a row of information linked to the position of a race Position
- Name
The Positions can be displayed 1-3 or 3-1 but the names don't move. Can
I keep them in sequence?
Stephen Guine - Don't think you quite understood what I meant. I can
record a macro that updates a sort and attach it to a button that I can
press to re-sort the data. How do I get the VBA code from this macro to
go into the "change" section you refer to?
Cheers for the help so far.
Bernard
---
Message posted from http://www.ExcelForum.com/
Quantcast