Named Ranges Not Working in Data Validation
 

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?

Named Ranges Not Working in Data Validation
 

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?

Named Ranges Not Working in Data Validation
 

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?

Named Ranges Not Working in Data Validation
 

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

Named Ranges Not Working in Data Validation
 

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

Named Ranges Not Working in Data Validation
 

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

Named Ranges Not Working in Data Validation
 

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

Named Ranges Not Working in Data Validation
 

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

Named Ranges Not Working in Data Validation
 

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?