Hi,
Two things:

1) The following doesn't re-calculate as I change the inputs,
I have to F2 and press enter each time
2) Also if there is an error it doesnt pick up the new lookup,
is ("Desktop\My Documents\Book10!Sheet1!a1:b3") valid?

Many Thanks


Function taz(a) As Variant
Dim res As Variant
res = Application.VLookup(a, Range("Sheet1!a1:b3"), 2, 0)

If IsError(res) Then
res = Application.VLookup(a, Range("Desktop\My
Documents\Book10!Sheet1!a1:b3"), 2, 0)
End If

If IsError(res) Then
taz = "Not found"
Else
taz = res
End If

End Function


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=505787

Try putting...

Application.Volatile

right after your Dim statement.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"T De Villiers" wrote:


Hi,
Two things:

1) The following doesn't re-calculate as I change the inputs,
I have to F2 and press enter each time
2) Also if there is an error it doesnt pick up the new lookup,
is ("Desktop\My Documents\Book10!Sheet1!a1:b3") valid?

Many Thanks


Function taz(a) As Variant
Dim res As Variant
res = Application.VLookup(a, Range("Sheet1!a1:b3"), 2, 0)

If IsError(res) Then
res = Application.VLookup(a, Range("Desktop\My
Documents\Book10!Sheet1!a1:b3"), 2, 0)
End If

If IsError(res) Then
taz = "Not found"
Else
taz = res
End If

End Function


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=505787