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?
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.
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.
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.
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.
[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.)
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.