Help with Sort Marco

I have a sort macro that sorts a cells A through Q based on the values in Q. The problem I am having is I have a formula in the cells of column E that performs a caluclation based off whats in Column B. When the sort is performed the formula gets sorted with the names in column B but the formula is still referencing where the name was at before the sort. Basically if the name was in cell B12 and now is in B1 after the sort the formula in E1 points to B12 which has a different name it.

Here is the code for the macro. I am pretty much a noob when it comes to macro's. I have also attached the spreadsheet. Any help would be most appreciated

Sub Overall_Rank_Sort()
'
' Overall_Rank_Sort Macro
'

'
    Range("A2:Q22").Select
    ActiveWorkbook.Worksheets("Weekly Reporting").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Weekly Reporting").Sort.SortFields.Add Key:=Range( _
        "Q3:Q22"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Weekly Reporting").Sort
        .SetRange Range("A2:Q22")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.SmallScroll Down:=22
    Range("A27:Q42").Select
    ActiveWorkbook.Worksheets("Weekly Reporting").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Weekly Reporting").Sort.SortFields.Add Key:=Range( _
        "Q28:Q42"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Weekly Reporting").Sort
        .SetRange Range("A27:Q42")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.SmallScroll Down:=15
    Range("A2:H2").Select
End Sub