Data Validation, Invalid Data Imput

[brodiemac]

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
"brodiemac" <[email protected]> wrote in message
news:[email protected]...
| 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


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

[brodiemac]

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 here:
>
> 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]

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 here:
>>
>> 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