Question: I send out a weekly stock report to the stock controller to update with the new stock items that come into the warehouse. In this Excel report the cell that contains a product code name always needs to begin with a standard prefix of ID- and must be at least 10 characters long. How do I ensure that the stock controller captures the Product ID’s correctly?
Answer: By using a Formula in a Data Validation to calculate what is allowed to be captured.
Applies To: MS Excel 2007, 2010
- Apply Data Validation to column A (ProductID) to ensure that Product ID’s are entered correctly in future
- Select the Data Tab, Go to Data Validation
- Enter the following criteria for the Data Validation Settings Window
- Allow – Custom
- Formula AND(LEFT(A2,3)=”ID-“,LEN(A2)>9)
- Enter the following criteria for the Data Validation Input Message Window
- Enter the following criteria for the Data Validation Error Alert Window
- Insert the following Product ID’s into Column A
- ID-23456789
- ID456878673
You will get an Error alerting you that the Incorrect Product ID has been entered
Note: The input message prompts you to enter the correct Product ID’s
If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email [email protected] and yours could be the next Tip of the Week.
Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchant-ability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations
