Named Ranges Not Working in Data Validation

  • Thread starter Thread starter dplum
  • Start date Start date
D

dplum

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
 
Sorry Pete this did not work.





- Show quoted text -
Click to expand...

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.
 
Maybe you defined cells_emplno incorrectly.

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

What was selected?
 
Dave,
The name correctly defines the range...

Dave Peterson said:
Maybe you defined cells_emplno incorrectly.

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

What was selected?
Click to expand...
 
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?
 
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.







- Show quoted text -
Click to expand...

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