Data Validation Headaches!

[Cam1234]

Hi All,

I have a list of 34 items, all of which are numbers that are 8 digits long.
For example 86978700. This 34 item list is the list of parts that I want to
make as 'acceptable' entries in this workbook.

What I've done is gone to Insert>Name>Define, and named the selection (which
happens to be G4:G38) as CH. Then I highlight cells A4:A65536 and go to
Data>Validation, select Allow:List and Source: =CH . When I click on any of
the 'A' cells a drop down list appears, and I can choose any of the 34 items.
When I try to key in any of the numbers except the first one, I get an error
message that says, "The value you entered is not valid. A user has
restricted values that can be entered into this cell".

People have already suggested that I manually type in the list under the
'Source:', but there seems to be a limit to the length of the list, and I
can't get all my 36 parts in. Someone also suggested that I uncheck the box
under the error alert tab that says 'show error allert after invalid data is
entered'. This will not work either, as then the users will not be
restricted from enterering bad data.

Thanks!... and please help!

 

[Dave Peterson]

There's a difference in the values in the data|validation list and the way
you're typing in the data.

If your list is really numbers, then make sure that the cell with data
validation is formatted as General.

If your list is really text (but look like digits), then make sure the cell with
the data validation is formatted as text.

But remember that just changing the format of a cell isn't enough to change the
value within the cell.

You can check the count of real numbers in G4:G38 with a formula like:
=count(g4:g38)
If this doesn't return 35, then you have at least one cell that isn't numeric.

You can check individual cells with:
=isnumber(g4)

 

[Cam1234]

That worked great. Thanks Dave.

There's a difference in the values in the data|validation list and the way
you're typing in the data.

If your list is really numbers, then make sure that the cell with data
validation is formatted as General.

If your list is really text (but look like digits), then make sure the cell with
the data validation is formatted as text.

But remember that just changing the format of a cell isn't enough to change the
value within the cell.

You can check the count of real numbers in G4:G38 with a formula like:
=count(g4:g38)
If this doesn't return 35, then you have at least one cell that isn't numeric.

You can check individual cells with:
=isnumber(g4)