Identify a worksheet from a provided range

Hello,

I'm going to try and explain myself here...

I have the below function:

Public Function HPROGDATE(Criteria As Range, HeaderRow As Integer)

Dim C As Range
Dim progress As String
progress = ""

For Each C In Criteria.Cells
    If C.Value <> "" Then progress = Mid(C.Address, 2, 1)
Next C

If progress <> "" Then HPROGDATE = Sheets("Progress_Tracker").Range(progress & Headerrow).Value


End Function

What this is doing is finding the latest entered value in a horizontal list, and returning the month (header row) this data was entered for.

There's two issues with my code:

1. to get the column from the Cell that has been updated, i have had to use the MID function. i tried using C.Column, but i didn't know how then to put that with the 'HeaderRow' variable that's passed in to identify the month. is there a tidier way of doing this?

2. The function is currently used on 1 sheet within a workbook, but the function automatically updates whenever something changes in the workbook. As the Criteria is passed in, it 'knows' that the range is on the 'Progress_Tracker' sheet. however i have had to explicitly state that sheet when returning my value, otherwise it uses the active sheet at the time of update. is there a way to identify the 'sheet' from the 'Criteria' range?

The formula appears in the worksheet as =HPROGDATE($D$4:$M$4,3)

edit - changed to solved