Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation Headaches!
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 InsertNameDefine, and named the selection (which happens to be G4:G38) as CH. Then I highlight cells A4:A65536 and go to DataValidation, 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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation Headaches!
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 wrote: 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 InsertNameDefine, and named the selection (which happens to be G4:G38) as CH. Then I highlight cells A4:A65536 and go to DataValidation, 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation Headaches!
That worked great. Thanks Dave.
"Dave Peterson" wrote: 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 wrote: 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 InsertNameDefine, and named the selection (which happens to be G4:G38) as CH. Then I highlight cells A4:A65536 and go to DataValidation, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
Excel Add-in Headaches! | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) |