question about sorting data

[placard]

If I link a column of data to another column in the worksheet by pasting special, How can I get this last column to sort from largest to smallest? Can it be made to sort automatically as the original column is changed?

 

[Yogi Anand]

On 2002-10-11 17:02, placard wrote:
If I link a column of data to another column in the worksheet by pasting special, How can I get this last column to sort from largest to smallest? Can it be made to sort automatically as the original column is changed?

Hi placard:

Welcome to the Board!

I used the following code for the Worksheet_SelectionChange -- I had my linked data in column D

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' 10/11/02 -- Yogi Anand (ySortLinkedColOnChange Macro)
    Range("D3").Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!

Yogi
This message was edited by Yogi Anand on 2002-10-11 17:20

 

[Alriemer]

Placard,

Not sure how you linked by pasting special, can you tell us which option you used?

One thing that might work here is INDEX(MATCH(RANK)))

ie, suppose A1:A5 contains 5 numbers.

In B1, enter =RANK(A1,$A$1:$A$5) and copy down to B5.
Then in C1:C5, enter 1 2 3 4 5.
Then in D1 enter =INDEX($A$1:$A$5,MATCH($C1,$B$1:$B$5,0)) and copy doen to D5.

D1:D5 should contain the original five numbers sorted from greatest to least. You can manipulate the order by changing the values in C1:C5 or by using SMALL, LARGE and the different settings of RANK, etc.

Hope this helps,
Alriemer