Cell Reference in Vlookup formula

[seahawk56]

I'm creating a production workbook that is using the VlookUp function to pull data from other workbooks. I would like to set it up so that when a new employee is added I can copy and past the formulas and it will adjust to pull the new employee's data. I'm learning how to apply constants and dimensions to make the formula short hand but I'm getting stuck on calling out the cell with the employee's name.

In the below examples the full Vlookup equation is really shortened by using the constants and dims but I'm having to change where to bring in the names (i.e. "Production Tracking\Andy C\Andy C ProdTrack"). As you can see by the VBA code (in the second box of "code") I'm having to call out the name in the code with (ActiveSheet.Range("P75").Value).

Thanks for the help,
Seahawk56

[SIZE=2]Full Vlookup equation:[/SIZE]
[SIZE=2]=IF(ISERROR(VLOOKUP($O77,'G:\ROC-CLAIMS\Clms Proc-Model Line\DMS\POS\Production Tracking\Andy C\Andy C ProdTrack_09166_09171.xls'!MonProd,2,FALSE)),0,VLOOKUP($O77,'G:\ROC-CLAIMS\Clms Proc-Model Line\DMS\POS\Production Tracking\Andy C\Andy C ProdTrack_09166_09171.xls'!MonProd,2,FALSE))[/SIZE]
 
[SIZE=2]VBA version with name cell as Dim: [/SIZE]
[SIZE=2]=IF(ISERROR(VLOOKUP($O76,MyStaff!MonProd,2,FALSE)),0,VLOOKUP($O76,MyStaff!MonProd,2,FALSE))[/SIZE]
 
[SIZE=2]What I would hope to go to:[/SIZE]
[SIZE=2]=IF(ISERROR(VLOOKUP($O76,MyLocation & StaffName(P75)!MonProd,2,FALSE)),0,VLOOKUP($O76,MyLocation & StaffName(P75)!MonProd,2,FALSE))[/SIZE]

Public Const MyFile As String = "POSdailyworkplanning"
Public Const MyLoc As String = "G:\ROC-CLAIMS\Clms Proc-Model Line\DMS\POS\Planned Work\"
Public Const MyProdFile As String = "ProdTrac"
Public Const MyProdLoc As String = "G:\ROC-CLAIMS\Clms Proc-Model Line\DMS\POS\Production Tracking\"
 
Public Sub ProdRollup(ByVal CellRef As String)
Dim MyWeek As String
Dim MyStaff As String
MyWeek = " " & MyProdFile & "_" & ConvertToJulian(ActiveSheet.Range("D1").Value) & "_" & ConvertToJulian(ActiveSheet.Range("E1").Value) & ".xls"
Debug.Print MyWeek
 MyStaff = MyProdLoc & (ActiveSheet.Range("P75").Value) & "\" & (ActiveSheet.Range("P75").Value) & MyWeek
Debug.Print MyStaff
End Sub