Hi...

I have 3 columns A - C.

In Column A I have values 1 -15 in descending order.
In Column B I have names of tools.
In Column C I have values that are subject to change.

What I need is some code that will look at the values column C and then
shift the tool names in Column B so that it creates a league table. Column A
can remain unchanged. This all needs to be intuitive and automated so that
there is no user control.

Thanks

GC

Gordon wrote:
Hi...

I have 3 columns A - C.

In Column A I have values 1 -15 in descending order.
In Column B I have names of tools.
In Column C I have values that are subject to change.

What I need is some code that will look at the values column C and then
shift the tool names in Column B so that it creates a league table. Column A
can remain unchanged. This all needs to be intuitive and automated so that
there is no user control.

Thanks

GC

Hi Gordon,

If the tool with the highest Column C value goes to the top of the
league table then you could try...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2:C16")) Is Nothing Then
Application.EnableEvents = False
On Error GoTo ERROR_HANDLER
Range("B2:C16").Sort Key1:=Range("C2"), _
Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.EnableEvents = True
End If
Exit Sub
ERROR_HANDLER: Application.EnableEvents = True
End Sub

If the tool with the lowest Column C value goes to the top of the
league table then you could try...


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2:C16")) Is Nothing Then
Application.EnableEvents = False
On Error GoTo ERROR_HANDLER
Range("B2:C16").Sort Key1:=Range("C2"), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.EnableEvents = True
End If
Exit Sub
ERROR_HANDLER: Application.EnableEvents = True
End Sub

Choose the appropriate code, copy it, right click the sheet tab, select
"View Code", paste the code, press Alt + F11 to get back to Excel.

Ken Johnson

E2: =INDEX($B$2:$B$16,MATCH(SMALL(C$2:C$16,ROWS($1:1)) ,$C$2:$C$16,0))

copy down

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Gordon" wrote in message
...
Hi...

I have 3 columns A - C.

In Column A I have values 1 -15 in descending order.
In Column B I have names of tools.
In Column C I have values that are subject to change.

What I need is some code that will look at the values column C and then
shift the tool names in Column B so that it creates a league table. Column
A
can remain unchanged. This all needs to be intuitive and automated so that
there is no user control.

Thanks

GC