Posts Topics Forums Images
Search videos from message boards Videos Search messages from microblogs Microblogs Search messages from imdb.com Imdb Search messages from yuku.com Yuku Search messages from lefora.com (free forums) Lefora
My account: Login | Sign Up
Loading... 

Thread: Data Validation: This could get messy...
Started 1 month, 3 weeks ago by monkeychuff
If anyone could help that would be great. In need data validation in a range of cells (lets keep it to A1 for the example please). The data being entered is a 9 digit number, which needs to satisfy the following: The 7th and 8th characters should be equal to an even number and be greater than zero, but less than 35. Example 1: If 123456789 is entered in the cell, this would return an ...
Site: MrExcel Message Board  MrExcel Message Board - site profile
Forum: Excel Questions  Excel Questions - forum profile
Total authors: 3 authors
Total thread posts: 51 post
Thread activity: no new posts during last week
Domain info for: mrexcel.com

Other posts in this thread:

mikerickson replied 1 month, 3 weeks ago
How about =AND(MOD(INT(A1)/100),100) < 35, ISEVEN(INT(A1)/100))
Size: 71 bytes
Customize:  Customize "<b>Reply 1</b>: Data Validation: This could get messy... :: Excel Questions :: MrExcel Message Board"

monkeychuff replied 1 month, 3 weeks ago
Quote: Originally Posted by mikerickson How about =AND(MOD(INT(A1)/100),100) < 35, ISEVEN(INT(A1)/100)) I can't get the formula to work, sorry mate.
Size: 687 bytes
Customize:  Customize "<b>Reply 2</b>: Data Validation: This could get messy... :: Excel Questions :: MrExcel Message Board"

mikerickson replied 1 month, 3 weeks ago
What happens when you put that formula in a cell. Does it return TRUE/FALSE as it should. Are the entries being validated numbers formatted as text or normal number numbers.
Size: 181 bytes
Customize:  Customize "<b>Reply 3</b>: Data Validation: This could get messy... :: Excel Questions :: MrExcel Message Board"

monkeychuff replied 1 month, 3 weeks ago
Quote: Originally Posted by mikerickson What happens when you put that formula in a cell. Does it return TRUE/FALSE as it should. Are the entries being validated numbers formatted as text or normal number numbers. Should I be putting the formula in 'custom data validation' or directly in the cell? It comes up ...
Size: 916 bytes
Customize:  Customize "<b>Reply 4</b>: Data Validation: This could get messy... :: Excel Questions :: MrExcel Message Board"

mikerickson replied 1 month, 3 weeks ago
That formula should go in the Validation box, but putting potential validation formulas in a cell is a way to test them and to catch typos, like the one in that formula. Try =AND(MOD(INT(A1)/100,100) < 35, ISEVEN(INT(A1)/100))
Size: 243 bytes
Customize:  Customize "<b>Reply 5</b>: Data Validation: This could get messy... :: Excel Questions :: MrExcel Message Board"

monkeychuff replied 1 month, 3 weeks ago
Quote: Originally Posted by mikerickson That formula should go in the Validation box, but putting potential validation formulas in a cell is a way to test them and to catch typos, like the one in that formula. Try =AND(MOD(INT(A1)/100,100) < 35, ISEVEN(INT(A1)/100)) It comes back with the message - 'A named ...
Size: 894 bytes
Customize:  Customize "<b>Reply 6</b>: Data Validation: This could get messy... :: Excel Questions :: MrExcel Message Board"

mikerickson replied 1 month, 3 weeks ago
There are no named ranges in that formula try =AND(MOD(INT(A1/100),100) < 35, ISEVEN(INT(A1)/100))
Size: 112 bytes
Customize:  Customize "<b>Reply 7</b>: Data Validation: This could get messy... :: Excel Questions :: MrExcel Message Board"

Peter_SSs replied 1 month, 3 weeks ago
Two comments: 1. I think Mike slightly misread the specs. The OP is testing the 7th and 8th digits, not the 8th and 9th. 2. ISEVEN is part of Analysis ToolPak & I don't think Data Validation likes it, although I don't get the particular error message the OP reported. Therefore, my suggestion: =AND(MOD(MID(A1,7,2)+0,2)=0,MID(A1,7,2)+0
Size: 592 bytes
Customize:  Customize "<b>Reply 8</b>: Data Validation: This could get messy... :: Excel Questions :: MrExcel Message Board"

Peter_SSs replied 1 month, 3 weeks ago
Re: Data Validation: This could get messy... Two comments: 1. I think Mike slightly misread the specs. The OP is testing the 7th and 8th digits, not the 8th and 9th. 2. ISEVEN is part of Analysis ToolPak & I don't think Data Validation likes it, although I don't get the particular error message the OP reported. ...
Size: 1,179 bytes
Customize:  Customize "<b>Reply 9</b>: Data Validation: This could get messy... :: Excel Questions :: MrExcel Message Board"

monkeychuff replied 1 month, 2 weeks ago
Quote: Originally Posted by mikerickson There are no named ranges in that formula try =AND(MOD(INT(A1/100),100) < 35, ISEVEN(INT(A1)/100)) Sorry mate, same problem, but Peter_SSs seems to have got it. Thanks for your help. Any ideas on this one: http://www.mrexcel.com/forum/showthread.php?t=4274 72 ...
Size: 1,079 bytes
Customize:  Customize "<b>Reply 10</b>: Data Validation: This could get messy... :: Excel Questions :: MrExcel Message Board"

 

Top contributing authors

Name
Posts
monkeychuff
27
user's latest post:
Data Validation: This could get...
Published (2009-11-14 15:27:00)
Quote: Originally Posted by Peter_SSs Not sure if this needs to be included with the previous Validation or not but you should be able to combine the two if needed - although you might possibly start to run into problems with the length of the formula. You are starting to ask an awful lot of the data Validation. Anyway, for this tasktry...
Peter_SSs
20
user's latest post:
Data Validation: This could get...
Published (2009-11-15 00:07:00)
Quote: Originally Posted by monkeychuff It comes back with an error - 'A Named Range You Specified Cannot Be Found'. For some reason, when I posted, the board has inserted a space in my formula. I have checked my source and it has no space so don't really know what happened there. Anyway remove the space from "EXA CT" Quote: Originally Posted by monkeychuff And Yes, I would like to combine with previous validation...
mikerickson
4
user's latest post:
Data Validation: This could get...
Published (2009-11-06 23:54:00)
There are no named ranges in that formula try =AND(MOD(INT(A1/100),100) < 35, ISEVEN(INT(A1)/100))

Related threads on "MrExcel Message Board":

Fix date in Data Validation list  MrExcel Message Board - site profile Excel Questions - forum profile Fix date in Data Validation list
Fix date in Data Validation list
Question RE: Opening of sheet and Data Validation  MrExcel Message Board - site profile Excel Questions - forum profile Question RE: Opening of sheet and Data Validation
Question RE: Opening of sheet and Data Validation
Nested Data Validation in Excel  MrExcel Message Board - site profile Excel Questions - forum profile Nested Data Validation in Excel
Nested Data Validation in Excel
Data validation List  MrExcel Message Board - site profile Excel Questions - forum profile Data validation List
Data validation List
DATA VALIDATION LIST BOX  MrExcel Message Board - site profile Excel Questions - forum profile DATA VALIDATION LIST BOX
DATA VALIDATION LIST BOX
Selection from Data Validation Creates next Validation List  MrExcel Message Board - site profile Excel Questions - forum profile Selection from Data Validation Creates next Validation List
Selection from Data Validation Creates next Validation List
Data validation-Do not allow "select" if cells con  MrExcel Message Board - site profile Excel Questions - forum profile Data validation-Do not allow "select" if cells con
Data validation-Do not allow "select" if cells con
Data validation - link to cell without losing validation...  MrExcel Message Board - site profile Excel Questions - forum profile Data validation - link to cell without losing validation option
Data validation - link to cell without losing validation option
Data validation-add list item to dependent validation list  MrExcel Message Board - site profile Excel Questions - forum profile Data validation-add list item to dependent validation list
Data validation-add list item to dependent validation list
Data Validation... Validation  MrExcel Message Board - site profile Excel Questions - forum profile Data Validation... Validation
Data Validation... Validation

Related threads on other sites:

What is data validation strategies for data mart validation  SURESHKUMAR.NET FORUMS - site profile Data Warehousing Basics - forum profile What is data validation strategies for data mart validation
What is data validation strategies for data mart validation
Custom data validation in InfoPath 2007. Right-click on...  Twitter / liewgm - site profile liewgm - forum profile Custom data validation in InfoPath 2007. Right-click on field, programming -&gt; data validation. http://tinyurl.com/ykfclud 8:57 PM Oct 8th from web
Custom data validation in InfoPath 2007. Right-click on field, programming -&gt; data validation. http://tinyurl.com/ykfclud 8:57 PM Oct 8th from web
InfoPath data validation - can use InfoPath built-in...  Twitter / liewgm - site profile liewgm - forum profile InfoPath data validation - can use InfoPath built-in checks (properties -&gt; data validation) 8:59 PM Oct 8th from web
InfoPath data validation - can use InfoPath built-in checks (properties -&gt; data validation) 8:59 PM Oct 8th from web
Writing a validation data sheet could be painfully boring...  Twitter / yessukumar - site profile yessukumar - forum profile Writing a validation data sheet could be painfully boring 6:52 AM Mar 2nd from web
Writing a validation data sheet could be painfully boring 6:52 AM Mar 2nd from web
Data validation on data validation function in...  Discussions in microsoft.public.excel.misc - site profile microsoft.public.excel.misc - forum profile Data validation on data validation function in microsoft.public.excel.misc
Data validation on data validation function in microsoft.public.excel.misc
Interesting way to do data-validation. Our validation...  Twitter / coldbox - site profile coldbox - forum profile Interesting way to do data-validation. Our validation plugin is based on a similar approach: http://www.intelligrape.com... 12:44 PM Jun 8th from TwitterFox
Interesting way to do data-validation. Our validation plugin is based on a similar approach: http://www.intelligrape.com... 12:44 PM Jun 8th from TwitterFox
Custom Data Validation in Microsoft Excel: You can use...  rfellows - Identi.ca - site profile rfellows - forum profile Custom Data Validation in Microsoft Excel: You can use custom data validation in Excel to prevent errors during .. http://bit.ly/HK37h
Custom Data Validation in Microsoft Excel: You can use custom data validation in Excel to prevent errors during .. http://bit.ly/HK37h
Could somebody explain the Data Validation wiki?  WordPress Support - site profile Plugins and Hacks - forum profile Could somebody explain the Data Validation wiki?
Could somebody explain the Data Validation wiki?
Messy Homes could make healthier kids -...  Twitter / thecryptic - site profile thecryptic - forum profile Messy Homes could make healthier kids - http://bit.ly/44bGAI (@ thebigjc @ joubin could messy codebases make healthier developers? :P ) 8:45 AM Nov 10th from TweetDeck
Messy Homes could make healthier kids - http://bit.ly/44bGAI (@ thebigjc @ joubin could messy codebases make healthier developers? :P ) 8:45 AM Nov 10th from TweetDeck
Data validation on data validation function  Microsoft Office (Excel, Word) forum - OfficeFrustration - site profile General Discussion - forum profile Data validation on data validation function
Data validation on data validation function
Thread profile page for "Data Validation: This could get messy..." on http://www.mrexcel.com. This report page is a snippet summary view from a single thread "Data Validation: This could get messy...", located on the Message Board at http://www.mrexcel.com. This thread profile page shows the thread statistics for: Total Authors, Total Thread Posts, and Thread Activity
 1   2   3   4   5   Next »