0

I have a 2 part problem that I am having issues with... mainly part 2. I have gotten the first part to work by itself.

1) I have a column which contains part numbers (###-#####-###). I am using data validation to make sure that there can be no duplicate part numbers entered in the column.

 Here is the custom formula that I am using:

 =COUNTIF($A:$A,A2)=1

2) I also want to force the values of the cells in the part number column into a custom format (###-#####-###). Any value that does not use the proper amount of numbers would prompt a message to the user. This one I am not completely sure how to accomplish.

Thanks in advance for your help!

1 Answer 1

0

There may be a cleaner way, but you can add to your Validation formula by using and AND() statement and stringing together the conditions for which you want to check. The following formula, as validation for Cell A1 would work:

=AND(COUNTIF($A:$A,A1)=1,ISNUMBER(0+LEFT(A1, 3)), MID(A1, 4, 1)="-", ISNUMBER(0+MID(A1, 5, 5)), MID(A1, 10, 1)="-", ISNUMBER(0+RIGHT(A1, 3)))

3
  • This does not appear to work. It will not let me put a valid number in the cells. I was going down a similar route with the AND inside of the COUNTIF statement with no luck yet. Sep 16, 2014 at 18:14
  • That's my fault.. The format I was testing this was slightly different than your format. I used ###-####-###... I will edit the formula.
    – JNevill
    Sep 16, 2014 at 18:19
  • Thanks a million! That seems to do the trick... and I understand it which is even better. Sep 16, 2014 at 18:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .