Data Validation, Invalid Data Imput

[Guest]

I'm using data validation for a drop down list in several sheets in a
workbook. I have them all accessing a list that is on worksheet one. Access
is through defining a name for the list called =salespeople. The problem is,
I can't get the Invalid Data Stop message to work. If I access the list
without the using the name =salespeople for the source and just use direct
cell references it works fine. Is this a glitch or by design?

 

[ilanr01]

I'm using data validation for a drop down list in several sheets in a
workbook. I have them all accessing a list that is on worksheet one.
Access
is through defining a name for the list called =salespeople. The
problem is,
I can't get the Invalid Data Stop message to work. If I access the
list
without the using the name =salespeople for the source and just use
direct
cell references it works fine. Is this a glitch or by design?

It worked for me both ways, see if in Error alert\Style you checked
"Stop"
Ilan

 

[Jim Rech]

I could not reproduce the problem using any Excel version: 2003, 2002, 2000
or 97. I created a list, named it "ValList" and on another sheet created a
list Data Validation using ValList as the list range. The Stop alert popped
up as it should when I manually entered something not in ValList.

--
Jim
| I'm using data validation for a drop down list in several sheets in a
| workbook. I have them all accessing a list that is on worksheet one.
Access
| is through defining a name for the list called =salespeople. The problem
is,
| I can't get the Invalid Data Stop message to work. If I access the list
| without the using the name =salespeople for the source and just use direct
| cell references it works fine. Is this a glitch or by design?

 

[Debra Dalgleish]

If there are blank cells in the Salespeople range, users will be able to
enter any value, without getting an error message.

If that's the problem, you could use a dynamic range as the list source.
There are instructions here:

http://www.contextures.com/xlNames01.html

 

[Guest]

That was it exactly. Once I unchecked ignore blank under the settings tab,
it worked like a charm. I have blanks in there for when we add new
salespeople. Thanks for the help!

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know how you solved the problem.