Re: Auto sort in real time

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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/

Relevant Pages

  • Re: sorting protected sheet not working
    ... Are all the cells in the range to sort unlocked? ... Do you have any cells that are locked that are touching this range? ... this is not possible even though the protection dialog box has an option to ... that works peachy but even when I say to allow sorting, ...
    (microsoft.public.excel)
  • Re: filtering and sorting in protected sheets
    ... macro that would unprotect the sheet, sort the data and then reprotect the ... you can protect the worksheet in code (if the autofilter ... > I have a sheet with some locked cells, the sheet is protected, but I have ... that filtering and sorting should be ...
    (microsoft.public.excel)
  • Re: Sorting issue
    ... Sort descending, then select just the cells with values and sort ascending. ... "Pat" wrote in message ... > and A3:A10 displaying the sorted recorded. ...
    (microsoft.public.excel.programming)
  • Re: Cant Sort a protected worhsheet
    ... Unlock the cells that should be sorted. ... If you want to keep the cells locked, but still allow the user to sort the data, ... If you have lots of different ways of sorting, maybe you could use this idea: ... "Dave Peterson" wrote: ...
    (microsoft.public.excel)
  • Re: Sorting Dates in Queries
    ... Use an extra field in your query just for the sorting, without displaying ... > to sort this data, it groups all April together and so ...
    (microsoft.public.access.queries)