|
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? |
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? |
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? |
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? |
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? |
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? |
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? |
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? |
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? |
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? |
| All times are GMT +1. The time now is 02:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com