I am unsure how to research my question or even how to title this question but l

Question

I am unsure how to research my question or even how to title this question but let me try and explain the formula I am looking for:

Say I have a spreadsheet that looks like this image attached below.

1. I need a formula in F5 to reference E5's time value against the rest of the times in column E (stage 1) E5:E14 and put the value in F5 of what placing they were based on all these times in column E. eg the value that the formula should come up with in F5 is 5 as the time in E5 is the 5th fastest time in column E.

Does this make sense?? Then I need to apply this same formula to all those cells under 'stage 1 place' - 'stage 2 place' - 'stage 3 place' so the table gives me the results of everything I am after automatically.

Any help is greatly appreciated meganjrose at gmail dot com if you want to get hold of mee directtly

Explanation / Answer

The only way to do this automatically would be to use an event macro.

If you're only making one entry at a time, perhaps something like this, put in the worksheet's code module:

Private Sub Worksheet_Calculate()

Dim rKey As Range

On Error GoTo Err_Handler

Application.EnableEvents = False

With Me.Cells

Set rKey = .Range("M4:M" & .Cells(.Rows.Count, 1).End(xlUp).Row)

End With

With Me.AutoFilter.Sort

With .SortFields

.Clear

.Add Key:=rKey, _

SortOn:=xlSortOnValues, _

Order:=xlAscending

End With

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.Apply

End With

Exit_Sub:

Application.EnableEvents = True

Exit Sub

Err_Handler:

Resume Exit_Sub

End Sub

If you're adding many times for a new rider, you may want to use the Worksheet_Change() event instead, and trigger the sort when an entry in a particular column is made.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.