Dealing with changing formula reference paths in shared folders

[mikenelena]

We have a main payroll workbook that pulls data (a mileage total) from a closed workbook in a shared folder. Several of us take turns running payroll, and the reference paths fall apart if the payroll is run from a computer other than the one at which the formula references were entered. I am looking for away to update those reference paths based on the current user/ computer.

I'm familiar with the environ ("username") in VBA, and I thought that capturing the current user into a cell might be a stepping stone to getting these reference formula paths correct. So I captured the user name in 1 cell, wrote the remainder of the path in another, and tried combining them, but that produced a text string and not the formula reference with desired data.

Maybe there is away to convert that text to a formula reference?

I'm open to other ideas too.

Thanks for any suggestions!

... Mike

 

[Worf]

Hi

Sub MyFormula()


MsgBox Environ("username"), 64, "This can be inserted in the formula."


' or get it from cell G53


[g55].Formula = "='c:\" & [g53] & "\[erpf.xlsm]disp'!$b$26"
End Sub