| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
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" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
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" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
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" wrote in message ... 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" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "JohnButt" wrote in message ... 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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
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" wrote in message ... 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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
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" wrote in message ... 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" wrote in message ... 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? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
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" wrote in message ... 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" wrote in message ... 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? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CountIF
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif NOT | New Users to Excel | |||
| How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
| edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
| COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
| COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Linear Mode
