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!
mmj1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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..

Saurabh...
data-validation.xlsx
0
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?
0
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
data-validation.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mmj1Author Commented:
I think this will work for me - thank you for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.