Hey There,

I have a tricky one here. In a nut shell, I think I am trying to create a valid circular reference.

In Cell C102 is a list of peoples names and in D102 is a list of the employee numbers. What I am trying to set up is that if the user selects a name in C102, if populates the Employee Number in D102. If the Employee Number is entered, then it will populate the Employee Name on C102.

I tried this first, which created a citrcular reference and crashed:
Dim FisUser As Range
Set FisUser = Range("C102")
Dim FisEid As Range
Set FisEid = Range("D102")
 
If Not Application.Intersect(FisUser, Range(Target.Address)) _
       Is Nothing Then
    Range("D102").Formula = "=IF(ISERROR(VLOOKUP(C102,FIS!A4:U25004,5,FALSE)),G1,VLOOKUP(C102,FIS!A4:U25004,5,FALSE))"
End If
   
If Not Application.Intersect(FisEid, Range(Target.Address)) _
       Is Nothing Then
    Range("C102").Formula = "=IF(ISERROR(VLOOKUP(D102,FIS!A4:W25004,19,FALSE)),G1,VLOOKUP(D102,FIS!A4:W25004,19,FALSE))"
End If
I thought something along these lines might work, but no good:
Dim FisUser As Range
Set FisUser = Range("C102")
Dim FisEid As Range
Set FisEid = Range("D102")
 
If Not Application.Intersect(FisUser, Range(Target.Address)) _
       Is Nothing Then
    Range("D102").Formula = "=IF(ISERROR(VLOOKUP(C102,FIS!A4:U25004,5,FALSE)),G1,VLOOKUP(C102,FIS!A4:U25004,5,FALSE))"
Or If Not Application.Intersect(FisEid, Range(Target.Address)) _
       Is Nothing Then
    Range("C102").Formula = "=IF(ISERROR(VLOOKUP(D102,FIS!A4:W25004,19,FALSE)),G1,VLOOKUP(D102,FIS!A4:W25004,19,FALSE))" 
End If
Any help would be greatley appreciated!!
Thanx a million!!