| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF & Named Range error Excel 2007
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. -- Thank You |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
UDF & Named Range error Excel 2007
Do you mean:
=AND(COUNTIF($A$2:$A$2000,A2)<=1,IsIPAddress(A2)) "HarryisTrying" wrote: 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. -- Thank You |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
UDF & Named Range error Excel 2007
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 "HarryisTrying" wrote: 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. -- Thank You |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
UDF & Named Range error Excel 2007
I made a typo
Function IsIPAdress(a1,optional a2) if ismissing(a2) then else end if end function IsIPAdress "Joel" wrote: 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 "HarryisTrying" wrote: 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. -- Thank You |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
UDF & Named Range error Excel 2007
yes I left the equal sign off (=) in my post but had it right in the formula
-- Thank You "Sam Wilson" wrote: Do you mean: =AND(COUNTIF($A$2:$A$2000,A2)<=1,IsIPAddress(A2)) "HarryisTrying" wrote: 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. -- Thank You |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
UDF & Named Range error Excel 2007
On Mon, 21 Sep 2009 08:23:03 -0700, HarryisTrying
wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
UDF & Named Range error Excel 2007
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)) -- Thank You "Ron Rosenfeld" wrote: On Mon, 21 Sep 2009 08:23:03 -0700, HarryisTrying wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2007 - Find formulas that use a named range | Excel Discussion (Misc queries) | |||
| Excel 2007 table named range does not include headers | Excel Programming | |||
| Excel 2007 Named Range - Naming convention | Excel Programming | |||
| Urgent... Getting error in accessing named ranges in excel 2007... | Excel Programming | |||
| Named range reference in VBA for Excel 2007 | Excel Programming | |||
Linear Mode
