Data Validation

Posted by ndy Aitch on November 14, 2000 11:59 AM

Data validation

I am trying to get a warning message to pop up when incorrect data is entered into an excel cell. I know it is possible to use data validation but my problem is I am looking to allow the following: 0.5, 1, S, and HS, nothing more. Does anyone know if there is a way?

Thanks in advance

Andy Aitch

Posted by Ben O. on November 14, 2000 1:01 PM

Enter 0.5, 1, S, and HS in separate cells. I'll use J1:J4 for this example. You can hide the column you enter them in if you don't want your users to see them.

Select the cells you want to apply data validation to. Select Validation from the Data menu. On the Settings tab, under Validation Criteria, select Allow > List. Where it says Source, enter =$J$1:$J$4 or just collapse the RefEdit box and select the range that contians 0.5, 1, S and HS.

On the Error Alert tab, make sure "Show Error Alert..." is checked. Also, make sure you have the "Stop" style selected, or else the user will only get a warning when he enters invalid data.

Press OK and you're done. If you want to make sure that the user can't delete values in the cells with Data Validation, lock them and protect the worksheet.

-Ben

Posted by Andy Aitch on November 15, 2000 10:24 AM

Ben, that's great. Thank you very much.

Kind Regards

Andy H