I have a workbook (Teams_master) which validates some data in another
workbook (complaints).
Teams_Master has a sheet called 'Officers' which contains a list of peoples
initials(col A), names(col B) & thier unit code(col C)
The data in the workbook complaints contains the initials & unit code and I
want to check this against the list in Teams_Master!Officers

The code below doesn't work!

Any help please - I've tried lots of variations and read lots of posts but
can't get it to work. I think I'm doing something simple wrong.

Thanks, Dean.

Function dean11(rdl46 As String, rdl47 As String)
Dim rdlk As String
Dim rdll As Range
rdll = "='E:\MANMON\TEAMS VALIDATION\Teams
Master\TEAMS_Master.xls'!Officers!$A$2:$C$30"
rdlk = Application.WorkbookFunction.VLookup(rdl47,
"[TEAMS_Master.xls]Officers!$A$2:$C$30", 3)
If rdl46 = rdlk Then rdlm = "" Else rdlk = "88888888"
dean11 = rdlk
End Function

This code does work (I think) and it inserts the UDF, copies it down then
tidys it up by copying the column, paste special/values then deleting the
origanal.

ChDir "E:\MANMON\TEAMS VALIDATION\Teams Master\xls"
Workbooks.Open Filename:= _
"E:\MANMON\TEAMS VALIDATION\Teams Master\xls\Complaints.xls"

Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Range("J1").Select
ActiveCell.FormulaR1C1 = "ERROR UNIT CODE"
Range("J2").Select
' check unit code against officer list
ActiveCell.FormulaR1C1 = _
"='E:\MANMON\TEAMS VALIDATION\Teams
Master\TEAMS_Master.xls'!dean11(RC[-1],RC[+1])"
Range("J2").Select
Columns("J:J").EntireColumn.AutoFit
Dim howmany As Long
howmany = WorksheetFunction.CountA(Range("A:A"))
Set SourceRange = Range("J2")
Set fillRange = Range("J2:J" & howmany)
SourceRange.AutoFill Destination:=fillRange
Columns("J:J").Select
Selection.Font.ColorIndex = 3
'copy & paste 88888888
Columns("K:K").Select
Selection.Insert Shift:=xlToRight
Range("K1").Select
ActiveCell.FormulaR1C1 = "ERROR UNIT CODE"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=88888888,88888888,"""")"
Range("K2").Select
Columns("K:K").EntireColumn.AutoFit
Set SourceRange = Range("K2")
Set fillRange = Range("K2:K" & howmany)
SourceRange.AutoFill Destination:=fillRange
Range("K2").Select
Columns("K:K").Select
Selection.Copy
Columns("J:J").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("K:K").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("J2").Select
Columns("J:J").Select
Selection.Font.ColorIndex = 3
Range("J2").Select

--
Regards, Dean