Link to home
Create AccountLog in
Avatar of mmj1
mmj1Flag for United States of America

asked on

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!
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

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
Avatar of mmj1

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of mmj1

ASKER

I think this will work for me - thank you for your help!