On Nov 23, 10:01 am, dplum wrote:
Hey Spike,
Tried thge Book name and got a message that Book and Sheet names cannot be
used in the formula -- so not sure what you did to make it work, but still a
no go here.



"Spike9458" wrote:
On Nov 22, 10:37 pm, dplum wrote:
Sorry Pete this did not work.

"Pete_UK" wrote:
Try it with the sheet name in front of the named range, i.e.:

Sheet1!cells_EMPLNO

Hope this helps.

Pete

On Nov 22, 9:07 pm, dplum wrote:
I want to use a named range in the Custom Data Valiadtion dialog.

If the Formula = "=COUNTIF(cells_EMPLNO,A2)<=1" it does not work.

However, if the formula = "=COUNTIF($A$2:$A$28,A2)<=1" it works.

Is there any way to get the Named Range option to work?- Hide quoted text -

- Show quoted text -

I played around with this and found that Pete was close, but instead
of the sheet name, try the book name;

"=COUNTIF(book1!cells_EMPLNO,A2)<=1"

... where "cells_EMPLNO" is the name of your named range. It worked
for me using "emplno" as the named range.

--Jim- Hide quoted text -

- Show quoted text -

What version of Excel are you using? Does it have the latest available
update?