Auto sort

stewy69

New Member
Joined
Nov 16, 2005
Messages
7
hi, i have a range of cells that are referencing from another sheet and so have this code in them

=LOOKUP(9.99999999999999E+307,Results!F25:IV25)

to display a sheet like this


Latest Operations Director Regional Director Site


99 David Jenkins Mike Sawyer 4D SW (Sussex)
98 David Jenkins Mike Sawyer 4D SW (Kent)
97 David Jenkins Mike Sawyer TW: Earlswood STW
87 David Jenkins Mike Sawyer 4D Average
96 David Jenkins Mike Sawyer AWE Aldermaston
95 David Jenkins Chris Tyerman Malton Oil Storage
93 David Jenkins Chris Tyerman Swaledale
92 David Jenkins Chris Tyerman Staithes Lane
91 David Jenkins Chris Tyerman St Andrews Quay
1 David Jenkins Chris Tyerman Naburn SAS

where the latest column is where that code is.

i want the latest column to auto update when i change the values on the other sheet but the code i have tried using does not seem to work which i think is because of there being code in the cells and not just values.

This is what i have used before

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Set the target Range
Set rng = Range("B:B")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
ActiveSheet.UsedRange.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub


thanks

Stuart
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
I think this will work OK instead of what is there (NB. the sheet will not be the Active one).

Code:
Private Sub Worksheet_Calculate()
    Worksheets("Sheet1").UsedRange.Sort Key1:=Range("B1"), _
    Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
 
Upvote 0
thanks brian that worked a treat, needed to move a bit of data around first but did exactly what i needed thanks
 
Upvote 0

Forum statistics

Threads
1,216,517
Messages
6,131,118
Members
449,622
Latest member
lejohnson93

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top