|
Format cell so number entered will show only one way
I have a speadsheet I am working on and would like some cells to be
"formatted" so when the user enters a number it will only accept it one way. For example: If the user enters the case number as 081234, the cell is currently formatted to accept it as 08-1234 (I set this up under FormatCellsNumberCustom, which works). However, if the user enters the case number WITH the hyphen I would like to use the DataValidation feature so an error alert pops up letting them know that no hyphen is needed. I was able to set this up, but if I enter the case with OR without the hyphen I get an error message for both, instead of just when using the hyphen. I see there is also a space to enter a formula under DataValidation but, unfortunately, I do not understand formulas very well. Can somebody please help? Thank you! |
Format cell so number entered will show only one way
I will try this. But can you read it to me in laymans terms, just I have a
better understanding? THANK YOU! "T. Valko" wrote: Try this: DataValidationAllowCustom Formula: =ISERROR(FIND("-",A1)) -- Biff Microsoft Excel MVP "Frustrated in AZ" <Frustrated in wrote in message ... I have a speadsheet I am working on and would like some cells to be "formatted" so when the user enters a number it will only accept it one way. For example: If the user enters the case number as 081234, the cell is currently formatted to accept it as 08-1234 (I set this up under FormatCellsNumberCustom, which works). However, if the user enters the case number WITH the hyphen I would like to use the DataValidation feature so an error alert pops up letting them know that no hyphen is needed. I was able to set this up, but if I enter the case with OR without the hyphen I get an error message for both, instead of just when using the hyphen. I see there is also a space to enter a formula under DataValidation but, unfortunately, I do not understand formulas very well. Can somebody please help? Thank you! |
Format cell so number entered will show only one way
You said that the cell is *custom formatted* to *display* a dash. While the
dash is *displayed* it is not part of the true underlying value of the cell. Using data validation prevents a user from actually entering a dash in the cell. If you use data validation and enter a "-" in the cell you'll get the error message. If you enter something without a "-" nothing will happen, no error message. -- Biff Microsoft Excel MVP "Frustrated in AZ" wrote in message ... I will try this. But can you read it to me in laymans terms, just I have a better understanding? THANK YOU! "T. Valko" wrote: Try this: DataValidationAllowCustom Formula: =ISERROR(FIND("-",A1)) -- Biff Microsoft Excel MVP "Frustrated in AZ" <Frustrated in wrote in message ... I have a speadsheet I am working on and would like some cells to be "formatted" so when the user enters a number it will only accept it one way. For example: If the user enters the case number as 081234, the cell is currently formatted to accept it as 08-1234 (I set this up under FormatCellsNumberCustom, which works). However, if the user enters the case number WITH the hyphen I would like to use the DataValidation feature so an error alert pops up letting them know that no hyphen is needed. I was able to set this up, but if I enter the case with OR without the hyphen I get an error message for both, instead of just when using the hyphen. I see there is also a space to enter a formula under DataValidation but, unfortunately, I do not understand formulas very well. Can somebody please help? Thank you! |
| All times are GMT +1. The time now is 02:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com