# Help With Data Validation in Excel **URL:** \ **Category:** Factual Questions\ **Created:** [May 10, 2006, 12:33am UTC](https://boards.straightdope.com/t/help-with-data-validation-in-excel/356149 "2006-05-10T00:33:38Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![ice1000](https://avatars.discourse-cdn.com/v4/letter/i/a3d4f5/32.png) [@ice1000](https://boards.straightdope.com/u/ice1000)\ **Post date:** [May 10, 2006, 12:33am UTC](https://boards.straightdope.com/t/help-with-data-validation-in-excel/356149/1 "2006-05-10T00:33:38Z")
I’m creating a form for my department and I want to implement data validation in one cell. The acceptable of values of this cell are A though G or a text value “No Show”. The values entered can range from a single letter to all letters or the text value. In this validation, the combinations are all equal (i.e. AG = GA) so I need to validate all the possible combinations. I tried using Pascal’s Triangle but I don’t think it applies since there can be a varied number of sets. I also thought of having a results table and doing a vlookup. Does anyone know of some VBA code to create my results list? Is there another way to do this validation? ---
**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\ **Post date:** [May 10, 2006, 1:09am UTC](https://boards.straightdope.com/t/help-with-data-validation-in-excel/356149/2 "2006-05-10T01:09:58Z")
What does the validation need to return? Is it in that same cell or will you just have a cell beside it that says something like “Valid” or “Invalid” work. Also how long are the letter entries? What I am trying to get at is just using some brute force function or lookup or is that not possible. I do lots of Excel development so throw out some more specs. ---
**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\ **Post date:** [May 10, 2006, 1:28am UTC](https://boards.straightdope.com/t/help-with-data-validation-in-excel/356149/3 "2006-05-10T01:28:18Z")
The following (ugly but worable) function checks to see if the cell A1 is “No Show”. If it is then it displays Valid. Otherwise it goes through and makes sure both the left digit and the right digit are in the series A - G. If it is, it is valid. Otherwise it displayes invalid. It only works with 2 letter combinations however. =IF(A1=“No Show”,“Valid”,IF(AND(OR(LEFT(A1)=“A”,LEFT(A1)=“B”,LEFT(A1)=“C”,LEFT(A1)=“D”,LEFT(A1)=“E”,LEFT(A1)=“F”,LEFT(A1)=“G”),OR(RIGHT(A1)=“A”,RIGHT(A1)=“B”,RIGHT(A1)=“C”,RIGHT(A1)=“D”,RIGHT(A1)=“E”,RIGHT(A1)=“F”,RIGHT(A1)=“G”)),“Valid”,“Invalid”)) ---
**Author:** ![ice1000](https://avatars.discourse-cdn.com/v4/letter/i/a3d4f5/32.png) [@ice1000](https://boards.straightdope.com/u/ice1000)\ **Post date:** [May 10, 2006, 1:42am UTC](https://boards.straightdope.com/t/help-with-data-validation-in-excel/356149/4 "2006-05-10T01:42:21Z")
> [@Shagnasty](#): > > What does the validation need to return? Is it in that same cell or will you just have a cell beside it that says something like “Valid” or “Invalid” work. Also how long are the letter entries? What I am trying to get at is just using some brute force function or lookup or is that not possible. > > I do lots of Excel development so throw out some more specs. I think brute force would work but I don’t want to code something to create all the possible combinations if someone else already has done something similar. The data validation (i.e. like the Data, Validation feature in Excel) is in the same cell and aimed at not allowing any entry in the cell that does not fall in a possible combination. So entries a,d d,e are allowed. Entries like fr are not allowed since one letter lies outside the range A-G and is therefore not a valid combination. The minimum value is 1 letter starting a A and ending at G. The maximum value is either: a) ‘No Show’ or b) ABCDEFG (all letters) Note that any combination of letters and the text value are mutually exclusive. Either a combination or the text, not both. I’m thinking if I have a list of all combinations, I can use an IF(ISNA(VLOOKUP)) formula to get a boolean in the Excel custom validation. ---
**Author:** ![ice1000](https://avatars.discourse-cdn.com/v4/letter/i/a3d4f5/32.png) [@ice1000](https://boards.straightdope.com/u/ice1000)\ **Post date:** [May 10, 2006, 1:58am UTC](https://boards.straightdope.com/t/help-with-data-validation-in-excel/356149/5 "2006-05-10T01:58:48Z")
> [@Shagnasty](#): > > The following (ugly but worable) function checks to see if the cell A1 is “No Show”. If it is then it displays Valid. Otherwise it goes through and makes sure both the left digit and the right digit are in the series A - G. If it is, it is valid. Otherwise it displayes invalid. It only works with 2 letter combinations however. > > =IF(A1=“No Show”,“Valid”,IF(AND(OR(LEFT(A1)=“A”,LEFT(A1)=“B”,LEFT(A1)=“C”,LEFT(A1)=“D”,LEFT(A1)=“E”,LEFT(A1)=“F”,LEFT(A1)=“G”),OR(RIGHT(A1)=“A”,RIGHT(A1)=“B”,RIGHT(A1)=“C”,RIGHT(A1)=“D”,RIGHT(A1)=“E”,RIGHT(A1)=“F”,RIGHT(A1)=“G”)),“Valid”,“Invalid”)) 2 combinations, that’s where I ran into trouble. My subject matter expert said it could be many combinations. Hmmm… You just gave me a formula for 2 out of 7. Using Pascal’s Triangle, there’s 21 possible combinations so I think I would have to use 21 of your formulas to get all different 2 value pairs (if my understand probability correctly). Bu thten I would have figure out which pairs to exclude that were previously evaluated. ---
**Author:** ![Omphaloskeptic](https://avatars.discourse-cdn.com/v4/letter/o/bcef8e/32.png) [@Omphaloskeptic](https://boards.straightdope.com/u/Omphaloskeptic)\ **Post date:** [May 10, 2006, 2:34am UTC](https://boards.straightdope.com/t/help-with-data-validation-in-excel/356149/6 "2006-05-10T02:34:39Z")
How about this: [spoiler](Is there any reason this code is going in spoiler boxes?) =IF(AND(A1\<\>“no show”, LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,“A”,""),“B”,""),“C”,""))),“invalid”,“valid”)[/spoiler] (I’ve used only the letters A-C; it should be obvious how to extend it.) ---
**Author:** ![hammos1](https://avatars.discourse-cdn.com/v4/letter/h/ba8739/32.png) [@hammos1](https://boards.straightdope.com/u/hammos1)\ **Post date:** [May 10, 2006, 9:59am UTC](https://boards.straightdope.com/t/help-with-data-validation-in-excel/356149/7 "2006-05-10T09:59:33Z")
We’re putting _Excel formulas_ in spoiler tags now? Don’t want to give away the ‘twist’ ending to that VLOOKUP function, I suppose? ---
**Author:** ![ice1000](https://avatars.discourse-cdn.com/v4/letter/i/a3d4f5/32.png) [@ice1000](https://boards.straightdope.com/u/ice1000)\ **Post date:** [May 10, 2006, 10:08am UTC](https://boards.straightdope.com/t/help-with-data-validation-in-excel/356149/8 "2006-05-10T10:08:43Z")
> [@Omphaloskeptic](#): > > How about this: > > [spoiler](Is there any reason this code is going in spoiler boxes?) > > =IF(AND(A1\<\>“no show”, LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( > A1,“A”,""),“B”,""),“C”,""))),“invalid”,“valid”)[/spoiler] > (I’ve used only the letters A-C; it should be obvious how to extend it.) That formula doesn’t give me the results I need. ---
**Author:** ![HornTraderHornBornInHounsditch](https://avatars.discourse-cdn.com/v4/letter/h/dec6dc/32.png) [@HornTraderHornBornInHounsditch](https://boards.straightdope.com/u/HornTraderHornBornInHounsditch)\ **Post date:** [May 10, 2006, 10:47am UTC](https://boards.straightdope.com/t/help-with-data-validation-in-excel/356149/9 "2006-05-10T10:47:32Z")
Just a thought, but rather than validating the data after its entered & throwing up error messages, why not create a form that allows the user to select only valid input. ---
**Author:** ![drhess](https://avatars.discourse-cdn.com/v4/letter/d/94ad74/32.png) [@drhess](https://boards.straightdope.com/u/drhess)\ **Post date:** [May 10, 2006, 12:15pm UTC](https://boards.straightdope.com/t/help-with-data-validation-in-excel/356149/10 "2006-05-10T12:15:15Z")
> [@hammos1](#): > > We’re putting _Excel formulas_ in spoiler tags now? Don’t want to give away the ‘twist’ ending to that VLOOKUP function, I suppose? The butler did it! ---
**Author:** ![Polerius](https://avatars.discourse-cdn.com/v4/letter/p/d78d45/32.png) [@Polerius](https://boards.straightdope.com/u/Polerius)\ **Post date:** [May 10, 2006, 1:26pm UTC](https://boards.straightdope.com/t/help-with-data-validation-in-excel/356149/11 "2006-05-10T13:26:56Z")
> [@ice1000](#): > > That formula doesn’t give me the results I need. It works when I try it. ---
**Author:** ![Enright3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/enright3/32/2897_2.png) [@Enright3](https://boards.straightdope.com/u/Enright3)\ **Post date:** [May 10, 2006, 3:19pm UTC](https://boards.straightdope.com/t/help-with-data-validation-in-excel/356149/12 "2006-05-10T15:19:04Z")
> [@ice1000](#): > > entries > a,d > d,e > are allowed. Entries like > fr > are not allowed since one letter lies outside the range A-G and is therefore not a valid combination. > > The minimum value is 1 letter starting a A and ending at G. > The maximum value is either: > a) ‘No Show’ > or > b) ABCDEFG (all letters) > > Note that any combination of letters and the text value are mutually exclusive. Either a combination or the text, not both. So could you legally have AABBCC in there too, or is each character limited to only a single existence? ---
**Author:** ![Gangster\_Octopus](https://avatars.discourse-cdn.com/v4/letter/g/a8b319/32.png) [@Gangster\_Octopus](https://boards.straightdope.com/u/Gangster_Octopus)\ **Post date:** [May 10, 2006, 4:22pm UTC](https://boards.straightdope.com/t/help-with-data-validation-in-excel/356149/13 "2006-05-10T16:22:39Z")
Maybe you just need to create a “SUBMIT” button that runs a macro similar to this (with additional bells and whistles as you please): > [@](#): > > Flag = “Valid” > StrLen = Len (Input) > x = 1 > While flag = valid > z = mid(Input,1,x) > If z \< “A” or z \> “G” then flag = “Invalid” > x = x + 1 > If x \> StrLen then goto SkipLoop > End While > > If Input = “No Show” then flag = valid > > SkipLoop: > End ---
**Author:** ![Gangster\_Octopus](https://avatars.discourse-cdn.com/v4/letter/g/a8b319/32.png) [@Gangster\_Octopus](https://boards.straightdope.com/u/Gangster_Octopus)\ **Post date:** [May 10, 2006, 4:23pm UTC](https://boards.straightdope.com/t/help-with-data-validation-in-excel/356149/14 "2006-05-10T16:23:51Z")
Whoops the 5th line of the code should read: z = mid(Input,x,1)