Error message when trying data validation

[Cam1234]

Hi All,

I'm trying to limit acceptable entries in a document I'm creating.
What I'm trying to do is make it so a user can only enter part numbers from
a specific list. The list is 34 part numbers long.

I've created the list and went to Insert>Name>Define, and named this list as
'CH', and then gone into Data>Validation then Allowed 'List', and the Source
as =CH for cells A4:A65536.

When this is done, I can choose any of the 34 parts from the list, but when
I try to manually enter any value except the first, I get an error message
saying "The
value you entered is not valid. A user has restricted values that can be
entered into this cell."

I want to be able to either type the number in, or choose it from the list.


Any ideas? I am using Excel 2000.

Thanks,

 

[Jim Thomlinson]

You can enter by hand but you must match the list exactly. That means that if
your list is text then your entry must also be text. Other than that make
sure that blank spaces are correct and that there is no blanks padded at the
ends of your part numbers in the list.

 

[Cam1234]

It seems my list is too long to be entered in. I got to part number 24, and
it wouldn't let me enter any more.

Any more ideas?

Thanks for your time so far.

 

[Gord Dibben]

Uncheck "Show error alert after invalid data has been entered" on the Error
Alert tab.


Gord Dibben MS Excel MVP