Validation Setting

C

Craig Helm

(Using Excel 97)

I have a payroll file that is sent away to a central processing site.
There area number of users on my site who enter the data. It is
important that some cells don't contain any more than 2 decimal
places.

I have tried Data Validation to ensure that there is no more than 2
decimal places in the entry.
I can see a 'Decimal' setting in the dialog box, but cannot see how to
then set it to a limited number of decimal points

I have already tried COPY ---> PASTE VALUES, with cell formatting set
to 2 decimal places. It looks okay, but the underlying value remains.

Thanks in advance!
Craig
 
P

Paul

Craig Helm said:
(Using Excel 97)

I have a payroll file that is sent away to a central processing site.
There area number of users on my site who enter the data. It is
important that some cells don't contain any more than 2 decimal
places.

I have tried Data Validation to ensure that there is no more than 2
decimal places in the entry.
I can see a 'Decimal' setting in the dialog box, but cannot see how to
then set it to a limited number of decimal points

I have already tried COPY ---> PASTE VALUES, with cell formatting set
to 2 decimal places. It looks okay, but the underlying value remains.

Thanks in advance!
Craig
Click to expand...

Cell formatting affects only how a value is displayed, NOT the underlying
value, as you have seen. This is not a fault, but simply the way it was
designed.

Data validation has its limitations anyway. Even if you succeeded in
defining a validation to no more than 2 decimal places, pasting data will
always override data validation.

An alternative approach would be to allow any input, but then round it to 2
d.p. in the formula(s) where you use it.
 
Top