• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation= Wanting to restrict a cell to only accept quarter:

S

smc001

Member
I am trying to the type of data entred into a cell: The entry can only be a combination of 1, 2, 3, 4. (The 1, 2, 3, 4 specifies quarters). Example they could enter 1, 2 or 1,4 or 1,2,3,4 or 1,3,4...get the idea..


At first I tried lists but, the list became long with all the combination possiblities: 1, 2, 3, 4 or 1, 2 or 1,3, 1, 4 or 2,3 etc...


This is what I came up with if I am understanding the choose formula option, but it does not work and theres a restriction on character for formula. So I can't enter all of it when I am trying to setup the data validation for the cell.


choose(1,"1")=1, choose(2, "2")=2, choose(3, "3")=3, choose(4, "4")=4, choose(1, 2, 3, 4,"1, 2, 3, 4")=1, 2, 3, 4, choose(1, 2, 3, "1, 2, 3")=1,2,3, choose(1, 2, “1, 2”)=1, 2, choose(1, 3, "1, 3")=1, 3, choose(1, 4, "1, 4")=1, 4, choose(2, 3, 4,”2, 3, 4”)=2, 3, 4, choose(2,3, “2, 3”)=2, 3, choose(2, 4, “2, 4”)=2, 4, choose(3, 4, "3, 4")=3, 4


I must be totally off base... Maybe there's a simpler formula I should be using... This is all foreign and even after review of the Chandoo Formula Book I purchased I am still lost. Please any help would be greatly appreciated! Thank You!
 
Hi ,


If I understand you correctly , the following are the combinations allowed :


1 1,2 1,3 1,4 2,3 2,4 3,4 1,2,3 1,2,4 1,3,4 2,3,4 and 1,2,3,4


Just 12 items should not make for a long list.


Or is 2,1 different from 1,2 ?


Narayan
 
You can setup a custom data validation condition to check if the combination is a valid Quarter combination. For ex. assuming the quarter combo is entered in cell A1,


you can use the formula


=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1",""),"2",""),"3",""),"4","")=""


This replaces all 1,2,3,4 in the value in A1 and see is the result is empty. (assumes you do not use , to separate the values).


See the example file here to understand how this works.


http://img.chandoo.org/playground/quarter-validation.xlsx
 
This is why I love this site that's an amazing solution Chandoo. But could you explain this one just a little more?
 
@John... The idea is like this. If a cell can only have a combination of 1,2,3,4, then when we substitute all of them with empty spaces, then that cell should also be empty. that is what the formula,


=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1",""),"2",""),"3",""),"4","")=""


is checking for. It is substituting 1,2,3 and 4s with empty spaces ("") and then checking if the end result is indeed empty.
 
@Chandoo sorry I still don't get it but I think what you could be saying is this.....


the validation formula initially entered characters into spaces but only where those spaces have been created by substitution of 1,2,3,4 so all other characters are rejected - somehow the spaces are then allowing 1,2,3,4 to be entered.


John
 
Hi John ,


I think it will be clear with an example.


The original problem was to use validation in a data entry cell to ensure that only a combination of the 4 digits 1,2,3 and 4 is entered. Thus , the valid entries would be 1 / 2 / 3 / 4 / 12 / 13 / 14 / 23 / 24 / 34 / 123 / 124 / 134 / 234 / 1234. Of course , you could also have other entries like 31 or 312 or ...


However , one thing common to all these valid entries would be that they would contain only the digits between 1 and 4 inclusive.


An entry like 7 would be invalid.


What Chandoo's formula is doing is that it is substituting all occurrences of the digits 1 , 2 , 3 and 4 with nulls.


In any valid entry , if all the valid digits are replaced by nulls , the resulting value would also be a null string.


Thus , after the substitution of all the valid digits by nulls , the resulting value is compared with a null string ; if TRUE , it means the original data entered was valid ; any non-null result can only mean that there were invalid digits in the original data.


Narayan
 
Thank you all for your wonderful input... This is the source report that data is pulled into other reports and in the source report I wanted a specific cell restricting inputing. I felt this would esure they are entering quarters allowed. such as; 1,2,3,4 or 3,4 etc.. preferrably in that numeric order..not 4,1... This i part two to my previous inquiry regarding quarter determination where on another report I have been able to break out from a projected training value, which quarters they will be using the monies... You guys are amazing!!!


I have entered the =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A30,"1",""),"2",""),"3",""),"4","")="" formula into the custom option and it works Beautiful but, it does not restrict an other numbers beyond 1-4 (I cant have anyone input 5,6,7,8 because there are no such quarters).


Any suggestions on to restrict as well as do I have to manual changed each formula cell or can I input something into the formula to allow it to recognized the cell instead of inputing A1, A2,A3 etc..?


I have alot of cells in a specific column which will need this formula... like A30, A31, A32, A33, A34, A35... this it will jump down too A44, A45, A46, A47... and continue throughout the source report in eleven different section. Please advise...
 
Hi ,


Are you saying that with the data validation formula in place , say in the cell A30 , you are still able to enter a value like 5 in that cell ?


Narayan
 
Thanks Narayan I appreciate the explanation and get it (sort of). It obscure and you have to marvel at this one as I think it's left field.

cheers
 
NARAYANK991 in response to your question Yes, I am still able to enter 5 into Cell A30... When I should be unable to enter anything other than 1,2,3,4 correct?...


I dont' want any one to be allowed to enter anything other than 1, 2, 3, 4, single or combination of the numbers in the numerical order.


Any suggestion why it's doing this? If its not supposed too? Please advise... thank you!
 
smc001,

I think you need to double check the formula you've entered. You sure you included the

=""

at the end of formula?

Is the cell reference correct?

Also, check the error alert tab...make sure you've got it setup to actually stop someone (since you can make it just a "soft" validation)

The formula is working correctly in my workbook (preventing any other value).
 
This is what I have under data validation criteria: allow custom... Checked off Ignore blanks, data: between, formula entered:


=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E30,"1",""),"2",""),"3",""),"4","")=""


Yes the style is set-up to STOP!


It was just working the first time I entered it but I accidental entered it with the A1 reference.. So I changed it to the cell that the data validation is residing, which is really column E row 30. It stopped any entry but single numbers (1 or 2) Is the A1 reference relevant?


I just tested my theory and but, changed the formula too E1.. It now allows me to enter in the 1,2,3,4, but continues on to allow 5,6,7,8,...I am running Excel 2010...What do you suggest...
 
Hi ,


I am not able to reproduce your problem.


I copied the formula from your post , entered it as validation in cell E30 , and tried out data entry in E30 ; it allows me to enter any combination of the digits 1 , 2 , 3 and 4 , but prevents me from entering any other digits like 5 , 6 , 7 ,...


If I uncheck the check box "Show error alert after invalid data is entered" , then I can enter any value.


Narayan
 
I tried unchecking that box and it did nothing... Question we all are under the impression that the numbers are being entered with the proper comma... correct? They must be separated with a comma... Would that have an effect??


Just tested my thought and it's the comma... If I input 1234 its all good...entery 12345 it stops me.


I need it to have the comma's how do we change the formula to allow for comma separation between numbers?
 
Hi ,


Put the following formula for your validation :


=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E30,",",""),"1",""),"2",""),"3",""),"4","")=""


The innermost SUBSTITUTE strips off all the commas.


Narayan


P.S. This validation will not ensure that entries such as "1,1" , "3,2" are prevented ; even such entries will be treated as valid.
 
AWESOME!!! It worked beautifully. Genius!!! One questions if I know the individuals entering into this cell they may or maynot attempt to use a space to separate the numbers? example: 1, 2, 3, 4. not 1,2,3,4...


Can the space also be incorporated as well into the formula...
 
Hi ,


if you wish to take care of spaces , just add a similar SUBSTITUTE(E30," ","") within the inner-most brackets i.e. where you now have :


SUBSTITUTE(E30,",","")


have the following instead :


SUBSTITUTE(SUBSTITUTE(E30," ",""),",","")


Narayan
 
I tried it and it works beatifully! Thank You!


This is what I put:


=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E31," ",""),",",""),"1",""),"2",""),"3",""),"4","")=""


Now does the data validation allow for row expansion... Let's say I set up so many rows within the column to have the data validation and then I need to add 10 more rows the formula will allow for the data validation to be applied to the newly expanded rows?
 
Hi ,


It will , but I think you might have to do it cell by cell , I am not sure.


Try selecting the whole range , and set up the validation. If it works , well and good. If it doesn't , just do it cell by cell.


Narayan
 
You must log in or register to reply here.
Back
Top