CountIF

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
Sat Mar 15 19:12:43 CDT 2008

>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 ?

--
Biff
Microsoft Excel MVP


"JohnButt" <JohnButt@discussions.microsoft.com> wrote in message
news:9AD08FEF-A5F8-4914-9F9D-129F8A2A9041@microsoft.com...
>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?

JohnButt
Sat Mar 15 19:23:00 CDT 2008

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

"T. Valko" wrote:

> >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 ?
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "JohnButt" <JohnButt@discussions.microsoft.com> wrote in message
> news:9AD08FEF-A5F8-4914-9F9D-129F8A2A9041@microsoft.com...
> >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
Sat Mar 15 20:00:40 CDT 2008

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)


--
Biff
Microsoft Excel MVP


"JohnButt" <JohnButt@discussions.microsoft.com> wrote in message
news:FE273600-EA2D-43CC-8DC8-1B5538F12BD2@microsoft.com...
> It could vary - 123567 - 234567 - 556777 - 776688 etc
>
> "T. Valko" wrote:
>
>> >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 ?
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "JohnButt" <JohnButt@discussions.microsoft.com> wrote in message
>> news:9AD08FEF-A5F8-4914-9F9D-129F8A2A9041@microsoft.com...
>> >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?
>>
>>
>>

JohnButt
Sat Mar 15 20:22:00 CDT 2008

Thank You - works perfectly - you deserve the MVP.

"JohnButt" wrote:

> 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
Sat Mar 15 22:44:53 CDT 2008

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JohnButt" <JohnButt@discussions.microsoft.com> wrote in message
news:417B4CD0-DC9E-4A9E-83CD-EECFECD8E0C8@microsoft.com...
> Thank You - works perfectly - you deserve the MVP.
>
> "JohnButt" wrote:
>
>> 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?

JohnButt
Tue Mar 18 13:57:00 CDT 2008

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" wrote:

> You're welcome. Thanks for the feedback!
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "JohnButt" <JohnButt@discussions.microsoft.com> wrote in message
> news:417B4CD0-DC9E-4A9E-83CD-EECFECD8E0C8@microsoft.com...
> > Thank You - works perfectly - you deserve the MVP.
> >
> > "JohnButt" wrote:
> >
> >> 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
Tue Mar 18 16:43:27 CDT 2008

>>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?



--
Biff
Microsoft Excel MVP


"JohnButt" <JohnButt@discussions.microsoft.com> wrote in message
news:8F5BDBCE-03DC-4325-B106-433E2915708C@microsoft.com...
> 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" wrote:
>
>> You're welcome. Thanks for the feedback!
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "JohnButt" <JohnButt@discussions.microsoft.com> wrote in message
>> news:417B4CD0-DC9E-4A9E-83CD-EECFECD8E0C8@microsoft.com...
>> > Thank You - works perfectly - you deserve the MVP.
>> >
>> > "JohnButt" wrote:
>> >
>> >> 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?
>>
>>
>>

JohnButt
Thu Mar 20 13:23:17 CDT 2008

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" wrote:

> >>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?
>
>
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "JohnButt" <JohnButt@discussions.microsoft.com> wrote in message
> news:8F5BDBCE-03DC-4325-B106-433E2915708C@microsoft.com...
> > 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" wrote:
> >
> >> You're welcome. Thanks for the feedback!
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "JohnButt" <JohnButt@discussions.microsoft.com> wrote in message
> >> news:417B4CD0-DC9E-4A9E-83CD-EECFECD8E0C8@microsoft.com...
> >> > Thank You - works perfectly - you deserve the MVP.
> >> >
> >> > "JohnButt" wrote:
> >> >
> >> >> 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
Thu Mar 20 15:49:06 CDT 2008

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


--
Biff
Microsoft Excel MVP


"JohnButt" <JohnButt@discussions.microsoft.com> wrote in message
news:0C3FC606-1DB9-4C2F-A3E7-0FF2FB3A1AF0@microsoft.com...
> 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" wrote:
>
>> >>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?
>>
>>
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "JohnButt" <JohnButt@discussions.microsoft.com> wrote in message
>> news:8F5BDBCE-03DC-4325-B106-433E2915708C@microsoft.com...
>> > 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" wrote:
>> >
>> >> You're welcome. Thanks for the feedback!
>> >>
>> >> --
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "JohnButt" <JohnButt@discussions.microsoft.com> wrote in message
>> >> news:417B4CD0-DC9E-4A9E-83CD-EECFECD8E0C8@microsoft.com...
>> >> > Thank You - works perfectly - you deserve the MVP.
>> >> >
>> >> > "JohnButt" wrote:
>> >> >
>> >> >> 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?
>> >>
>> >>
>> >>
>>
>>
>>