placard

New Member
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?

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Yogi Anand

MrExcel MVP
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

Code:
``````Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' 10/11/02 -- Yogi Anand (ySortLinkedColOnChange Macro)
Range("D3").Select
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

Board Regular
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

Replies
0
Views
249
Replies
1
Views
122
Replies
20
Views
384
Replies
1
Views
116
Replies
4
Views
126

1,195,622
Messages
6,010,743
Members
441,567
Latest member
Flitbee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back