Excel 2010 setup spreadsheet for users to enter data with certain restrictions

I need to set up a spreadsheet to provide to various users to complete on a monthly basis.  The have to fill in only about 7 columns of information - I would like to restrict what cells they can access and what can be entered -- for example:
Customer Name -- text restricted to 40 characters
Comments - restricted to 100 characters (but want data to word wrap)
Cost - restricted to whole dollars - format with $ and no decimal places
Sold Date:  restricted to a mm/dd/yy format

I looked a data validation but that didn't seem to work for me.  Any direction you could provide would be appreciated.
I have done this in Word before - but not in Excel.  Thank you!
Who is Participating?
Saurabh Singh TeotiaCommented:
Okay here is the thing if you want to check the value while entering the value their is no way that i can think you can do in excel till the time you give him a userform option..Otherwise even if i write a macro it will check values only after once it has entered...

In additional now in the enclosed workbook you can give a note like this to the user as shown while he starts to enter the data or select the data....

Fixed the word wrap as i applied that formatting earlier it wasn't set..

In additional now if you see the workbook is protected and you can only work on cells highlighted in yellow and this i done by pressing ctrl+1 and unchecking the option of protection in my desired range even after sheet protection..sheet password is -->abc
Saurabh Singh TeotiaCommented:
I'm not sure how you are applying this data validation..Enclosed is the example for your reference where i applied data validation with combination of cell formatting and it does what you are looking for..

Please refer to row highlighted in yellow where i have applied this to do what you have asked..

mmj1Author Commented:
Thanks for the response and example.
In the Customer Name field for example - the user can type more than 40 characters and it does not advise them until the hit tab or enter that it is not acceptable - also the comments (does the same thing and does not word wrap).
Also, if I use data validation and I able to allow users access to only the cells to enter data and protect the remainder of the sheet?
mmj1Author Commented:
I think this will work for me - thank you for your help!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.