Data Validation for Prices in Cells

R

Ryan H

Out of all the years I've used Excel I've never used data validation before
and need help. I have a list of part numbers (Col.A), part descriptions
(Col.B), and part prices (Col.C). I want to ensure the user enters a valid
price in Col. C. It can be any price, but must be numeric. I have Col.C
formatted as Accountanting. And ideas?

Thanks in Advance!
 
E

EricG

Data/Validation --> Select Decimal? That will ensure the user enters a
decimal number. You can use the min/max ranges to make sure the number is
reasonable. The only thing this won't do is ensure that the decimal number
as only two places, so the user could enter 45.32384.

If you want to enforce the two decimal place requirement, you would probably
have to use the "Custom" selection and create an appropriate formula.

HTH,

Eric
 
K

ker_01

With the custom formula option, you can do something like:

=AND(A1 = round(A1,2), A1>=3.50, A1<4.50)
this forces the number to be 2 decimal places, and at least 3.5 and no more
than 4.5

note: This doesn't work well for percentages (round(A1,4) for a percent with
2 decimals) because if the entry fails against the data validation rule, when
it highlights the cell for re-entry, it does not treat the newly entered
number as a percent, so you are pretty much guaranteed to be outside your
min/max value ranges.

HTH,
Keith
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top