Automatically ranking

I have a worksheet, we can call it worksheet 1, that ranks name in column "A" and he or shes score in column "B". These score are driven from another date worksheet, worksheet 2, within the workbook. I would like have column A and B of worksheet 1 ranked automatically as the data is updated. Can this be done? What would be the best formula to insure accuracy?

Thanks,
Bryan

if the scores in column B are used to determine the rank, then why not just use the rank formula eg

=RANK(B1,$B$1:$B$5)

or are you after something more detailed?

There is a added wrinkle. The score in column b is a formula of a goal number minus the cumulative score to date.

That would be fine if the name automatically ranks with the score. For example the the persons name is in column A and the score is in column b. And these name and scores are updated frequently from a data sheet.

does the name in column A correspond to the score in column B, and get updated at the same time? If so, the rank formula should still work. If not, how are they updated? if you upload an example it might be easier to test

Ok. The example workbook has two worksheets. Leaderboard and Scoring grid. On the Leaderboard worksheet in column B is the team name. In column D is the cumulative score based on day, in this example it is day 4 and driven from the second worksheet. The score in D minus the goal score in column E. I would like to have the name in column B and the score in column d update automatically daily. Hope this makes sense. Thanks for the help.

Hi
this formula in column A gives rank:

=RANK(D4,D$4:D$6,1)

If they must be sorted in order, convert all your references to absolute from relative (ie ='Scoring Grid'!$A$3 not ='Scoring Grid'!A3) and sorth the range. This can be dome with a simple macro if you like.

Terrific. That works for the rank. Is there a formula that will automatically reorder these for lowest to highest or vice versa so I do not have to re sort? Keep in mind I put the example in order from lowest to highest but that will not always be the case.

it could be done with a formula I think, but is probably easiest to do with a sort macro
convert all your formulas from relative to absolute references, then try these macros
you will need to adjust the sort range and sort key to reflect the data you want to sort on

Sub sort_ascending()
    
Dim SortRange, SortKey
SortRange = "A4:D6" 'range of data to sort
SortKey = "A4" 'cell to sort on

    ActiveWorkbook.Worksheets("LEADERBOARD").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("LEADERBOARD").Sort.SortFields.Add Key:=Range(SortKey), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("LEADERBOARD").Sort
        .SetRange Range(SortRange)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
Sub sort_descending()
    
Dim SortRange, SortKey
SortRange = "A4:D6" 'range of data to sort
SortKey = "A4" 'cell to sort on

    ActiveWorkbook.Worksheets("LEADERBOARD").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("LEADERBOARD").Sort.SortFields.Add Key:=Range(SortKey), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("LEADERBOARD").Sort
        .SetRange Range(SortRange)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Thanks for all your help. It will most likely be tomorrow. It is late hear.