validating a cell for $ amount

[exceltadpole]

Is it possible to set up a validation for cell b3 so that -
it only accepts a $ currency value
with 2 decimal places (if necessary)
examples 32 or 32.40 or 32.4
it accepts a $ sign but that is not required
once the user exits cell b3, a $ sign is also displayed in the cell
a comma is also accepted if the user enters $1,345 or 1,345.50 or 1,345.5
the user may also enter $1345 or 1345.50 or 1345.5
once the user exits cell b3, a comma is also displayed in the cell if appropriate
so the cell will only accept the characters
1234567890,.$
any other characters will generate an error message "Please enter a valid $ amount."

 

[Tom Urtis]

Curious - - why don't you just format the cell the way you want with the currency and all, and then validate for decimal in the DV dialog?

 

[bydesign]

Exceltadpole

1) Format the cell for currency
Right click on the cell / format cell / number tab / currency

2) use data valadation
Data / valadation / settings tab

change dropdown to "allow decimal", enter a min, and max value... (i.e. .01min and max 10000000000000)

on error alert tab, enter a message that you want if the user enters the wrong type of data

 

[exceltadpole]

Ok.
Once again, I did not use my brain.

I did format the cell as Currency but did not realize that further data validation would acheive the desired results.