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?

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?

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?

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

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

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?

Maybe you defined cells_emplno incorrectly.

I'd try
Edit|Goto and type in cells_emplno
and hit enter

What was selected?

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?

--

Dave Peterson

Dave,
The name correctly defines the range...

"Dave Peterson" wrote:

Maybe you defined cells_emplno incorrectly.

I'd try
Edit|Goto and type in cells_emplno
and hit enter

What was selected?

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?

--

Dave Peterson

I've never seen it fail.

Maybe you want to give more details.

What's the cells_emplno address?
What does it refer to?
Is the sheet with the cell with data validation the same sheet as cells_emplno?

What "doesn't work" mean?
What happens when you try to use it?

dplum wrote:

Dave,
The name correctly defines the range...

"Dave Peterson" wrote:

Maybe you defined cells_emplno incorrectly.

I'd try
Edit|Goto and type in cells_emplno
and hit enter

What was selected?

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?

--

Dave Peterson


--

Dave Peterson