Sorry for guessing wrong about the =na()/NA stuff.

But glad you got it working.

tommylux wrote:

Thanks dave,

Just what I was looking for, I have changed it a little you may have
confused "NA" for isna which is what the user would type in, forgive
the formatting:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myLookupRng As Range

With Target

If .Cells.Count 1 Then Exit Sub

If Application.Intersect(.Cells, Range("F:F")) Is Nothing
Then
Exit Sub
End If

If Application.Intersect(.Cells, Range("1:1")) Is Nothing Then

If Target.Formula = "" Then
.Offset(0, 1).Formula = ""
Else

Set myLookupRng = Me.Parent.Worksheets("Batch
Links").Range("A:H")

.Offset(0, 1).Formula _
= "=IF(OR(ISBLANK(" & .Address & ")," & .Address &
"=""NA""),"""",VLOOKUP(" & .Address & "," &
myLookupRng.Address(external:=True) & ",8,FALSE))"
End If
End If
End With
End Sub

--

Dave Peterson