Data Validation for Text Allowed using Nested IF(AND) statements

Question

Okay, so I have a couple of cells that I want to limit the number of characters allowed, based on the input of another cell.  If the number in primary cell H4 are 10 through 19, I want cell I4 to allow up to 12 characters HOWEVER, I also want to limit the input in cell J4 (up to 8 characters).  If there IS input in J4, then cell I4 has to reduce its character allowance to 8.  How do I do this, then, if the input in cell H4 are the numbers 30 through 39, then I want to limit the text character in cell I4 to 3 and J4 to 0; then, finally, if the number input in cell H4 is 40 through 79, I want to limit the character input in cell I4 to 1 and J4 to 0.  How do I put that in a formula????  I tried putting the following in data validation, =IF(AND(H4>=40,H4<79,AND(LEN(I4)<=1,LEN(J4)=0,IF(AND(H4>=30,H4<=39,AND(LEN(I4)<=3,LEN(J4)=0,IF(AND(H4>=10,H4<=19,AND(LEN(J4)>0,LEN(I4)<=8,LEN(I4)<=12))

 But it's not working (says I have an error) and my eyes are beginning to cross.

Answer 1

sorry, keep forgetting to change ; to , (due to regional settings)

I have posted above the correct ones, however * should remain as they were.

Now, use the correct one, for I4: =MIN(IF(AND((10<=H4)*(H4<=19)),12,100),IF(LEN(J4)>0,8,100),IF(AND((30<=H4)*(H4<=39)),3,100),IF(AND((40<=H4)*(H4<=79)),1,100))    ,and notice how it is affected by len(J4) as you said.

You could also try this:  =MIN( MIN( IF(AND((10<=H4)*(H4<=19)),12,100),IF(LEN(J4)>0,8,100) ) ,IF(AND((30<=H4)*(H4<=39)),3,100),IF(AND((40<=H4)*(H4<=79)),1,100)) .

If this doesn't work either explain better to fix it. Maybe post some tests for testing as you expect them to be.

Answer 2

Data Validation for I4: =MIN(IF(AND((10<=H4)*(H4<=19));12;100);IF(LEN(J4)>0;8;100);IF(AND((30<=H4)*(H4<=39));3;100);IF(AND((40<=H4)*(H4<=79));1;100))

for J4: =MIN(IF(AND((10<=H4)*(H4<=19));8;100);IF(AND((30<=H4)*(H4<=39));0;10;0);IF(AND((40<=H4)*(H4<=79));0;100))

but better break it in the smaller IFs()  first and have an idea what is going on...

cheers

Apostolos55:

I had to tweak the formulas just a little (convert * into commas and ; into commas).  The second formula works great using it in the Data Validation tool, but the first formula, for I4, doesn't limit the input at all.  Here is what I am putting into the data validation box for I4, using Custom selection:

=MIN(IF(AND((10<=H4),(H4<=19)),8,100),IF(AND((30<=H4),(H4<=39)),3,100),IF(AND((40<=H4),(H4<=79)),1,100))

Answer 3

Data Validation for I4: =MIN(IF(AND((10<=H4)*(H4<=19)),12,100),IF(LEN(J4)>0,8,100),IF(AND((30<=H4)*(H4<=39)),3,100),IF(AND((40<=H4)*(H4<=79)),1,100))

for J4: =MIN(IF(AND((10<=H4)*(H4<=19)),8,100),IF(AND((30<=H4)*(H4<=39)),0,10,0),IF(AND((40<=H4)*(H4<=79)),0,100))

but better break it in the smaller IFs()  first and have an idea what is going on...

cheers