CountIF

[JohnButt]

I have a column named PTS Number
I have used the custom validation to ensure that duplicate values are not
repeated.
Is there a way of combining the COUNTIF formula to ensure that at least 6
numeric characters are imputed?

 

[T. Valko]

Is there a way of combining the COUNTIF formula to ensure

that at least 6 numeric characters are imputed?

What is the format of these 6 numbers? Will there be any leading 0s? Is this
a legitimate number: 000000 ?

 

[JohnButt]

It could vary - 123567 - 234567 - 556777 - 776688 etc

 

[T. Valko]

Assume the range of interest is A1:A10. Use this as the validation formula:

=AND(INT(A1)=A1,AND(A1>=100000,A1<=999999),COUNTIF($A$1:$A$10,A1)<2)

 

[JohnButt]

Thank You - works perfectly - you deserve the MVP.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[JohnButt]

Hello once again

The formula you provided worked fine until I entered the text 'MainID' into
A1 - have tried all sorts of permutations but have failed to get the results
wanted - do you have any suggestions.

 

[T. Valko]

to ensure that at least 6 numeric characters are imputed

worked fine until I entered the text 'MainID' into A1

Hmmm...

The text "MainID" isn't 6 digits, is it? <g>

So, does that mean the entry could be *either* a text string or a 6 digit
number?

 

[JohnButt]

Thanks for the response - at least you didn't call me a dope!

I have generally always used a database with defined fields but
unfortunatley the company I am presently working for have Office installed
but without Access so I have had to convert the database I already had into
Excel.

The reason - as you have probably already guessed - for placing the text in
A1 as 'Main Id' - is to give a descriptive title to the entries below it -
which will always be numeric.

I think I may have worked out how to solve the problem - purely by taking
the validation off the single cell A1. At least it seems to work.

You may have another suggestion.

 

[T. Valko]

Yes, you did the right thing. If A1 is just the column header do not apply
the validation to that cell.