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?

brodiemac Wrote:
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


--
ilanr01
------------------------------------------------------------------------
ilanr01's Profile: http://www.excelforum.com/member.php...o&userid=26797
View this thread: http://www.excelforum.com/showthread...hreadid=465553

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
"brodiemac" wrote in message
...
| 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?

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 he

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


brodiemac wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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" wrote:

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 he

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


brodiemac wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

brodiemac wrote:
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" wrote:


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 he

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


brodiemac wrote:

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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html