UDF & Named Range error Excel 2007

[HarryisTrying]

I have created a user defined function called IsIPAddress and it works fine
if I put =IsIPAdress(a2) in column B, for example. It also works if I use
data validation, settings, Allow and select custom and put in the formula
IsIPAddress. If I put an equals sign in it doesn't work =IsIPAddress

I am also trying to see if there are duplicates so I have tried the following:
=AND(COUNTIF($A$2:$A$2000,A2),=1,IsIPAddress)

if I do the compound equation above or the =IsIPAdress I get the following
error "A named range you specified cannot be found".

Why is it looking for a name range when I have a UDF with that name? Is
there a way to prevent this error?

If I put the =IsIPAddress in an adjacent cell B2 (for example) the IP check
works great. Then I put this in the formula using the Validation Icon and it
works,
=and(countif($a$2:$a:2000,a2)<=1,b2)

I would like to have one compound formula using the Validation Icon but
can't seem to figure this out.

 

[Sam Wilson]

Do you mean:

=AND(COUNTIF($A$2:$A$2000,A2)<=1,IsIPAddress(A2))

 

[Joel]

One more item. to test if the optional parameter exits use IsMissing

Function IsIPAdress(a1,optional a2)
if ismissing(IsIPAdress) then

else

end if

end if

 

[Joel]

I made a typo


Function IsIPAdress(a1,optional a2)
if ismissing(a2) then

else

end if

end function

IsIPAdress

 

[HarryisTrying]

yes I left the equal sign off (=) in my post but had it right in the formula

 

[Ron Rosenfeld]

I have created a user defined function called IsIPAddress and it works fine
if I put =IsIPAdress(a2) in column B, for example. It also works if I use
data validation, settings, Allow and select custom and put in the formula
IsIPAddress. If I put an equals sign in it doesn't work =IsIPAddress

I am also trying to see if there are duplicates so I have tried the following:
=AND(COUNTIF($A$2:$A$2000,A2),=1,IsIPAddress)

if I do the compound equation above or the =IsIPAdress I get the following
error "A named range you specified cannot be found".

Why is it looking for a name range when I have a UDF with that name? Is
there a way to prevent this error?

If I put the =IsIPAddress in an adjacent cell B2 (for example) the IP check
works great. Then I put this in the formula using the Validation Icon and it
works,
=and(countif($a$2:$a:2000,a2)<=1,b2)

I would like to have one compound formula using the Validation Icon but
can't seem to figure this out.

You are omitting the argument to your UDF.

=IsIPAddress

is not the same as

=IsIPAddress(cell_ref)

--ron

 

[HarryisTrying]

Ooops, I typed it wrong in my post but I did include the argument in the Data
Validation formula.. I get the Named Range error. Temporarily I put the
IsIPAddress test in another column (D) and then put this formual in and it
works fine.
=AND(COUNTIF($A$2:$A$2000,A2),=1,(D2))