 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / March 2008CountIF
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? >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 ?  SignatureBiff Microsoft Excel MVP >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? It could vary - 123567 - 234567 - 556777 - 776688 etc > >Is there a way of combining the COUNTIF formula to ensure > >that at least 6 numeric characters are imputed? [quoted text clipped - 7 lines] > > Is there a way of combining the COUNTIF formula to ensure that at least 6 > > numeric characters are imputed? 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) SignatureBiff Microsoft Excel MVP > It could vary - 123567 - 234567 - 556777 - 776688 etc > [quoted text clipped - 12 lines] >> > 6 >> > numeric characters are imputed? Thank You - works perfectly - you deserve the MVP. > 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? You're welcome. Thanks for the feedback! SignatureBiff Microsoft Excel MVP > Thank You - works perfectly - you deserve the MVP. > [quoted text clipped - 3 lines] >> Is there a way of combining the COUNTIF formula to ensure that at least 6 >> numeric characters are imputed? 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. > You're welcome. Thanks for the feedback! > [quoted text clipped - 5 lines] > >> Is there a way of combining the COUNTIF formula to ensure that at least 6 > >> numeric characters are imputed? >>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? SignatureBiff Microsoft Excel MVP > Hello once again > [quoted text clipped - 15 lines] >> >> least 6 >> >> numeric characters are imputed? 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. > >>to ensure that at least 6 numeric characters are imputed > >worked fine until I entered the text 'MainID' into A1 [quoted text clipped - 25 lines] > >> >> least 6 > >> >> numeric characters are imputed? Yes, you did the right thing. If A1 is just the column header do not apply the validation to that cell. SignatureBiff Microsoft Excel MVP > Thanks for the response - at least you didn't call me a dope! > [quoted text clipped - 45 lines] >> >> >> least 6 >> >> >> numeric characters are imputed? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.