Cell does not always recalculate

Hi folks, my name is Myles and I am a s/w engineer from Christchurch in New Zealand's South Island.

I have an issue with a cell not updating and wondered if anyone could help. I have searched the forum and not found a solution that works.

Here is my function

Function stops(backNumber As Integer, sheet As String)

    stops = Application.WorksheetFunction.Lookup(backNumber, ActiveWorkbook.Sheets(sheet).Range(myRange("A")), ActiveWorkbook.Sheets(sheet).Range(myRange("O")))
    
End Function

Function myRange(column As String) As String
    myRange = "$" + column + "$4:$" + column + "$203"
End Function

The idea is that you give it an index and a sheet name and it goes to the sheet, looks up the index in column A and returns the value from column B.

I am using the function in Sheet1 and I have the two columns of values in Sheet2.

When I update a value in Sheet2 column O it does not update the value that the function returns on Sheet1.

• Selecting the Sheet1 cell and pressing Enter does not work
• Selecting Sheet1 and pressing F9 does not work
• None of the various macros/functions that call Calculate seem to work
• Selecting the Sheet1 cell, pressing F2 then Enter DOES work

I'm a bit stuck for ideas now so any help would be much appreciated.