Custom Validation Formula

[Mbourque94]

I need to create a custom formula for a validation test with the requirement being 4 digits and a value within the range of 1000 and 6900. This could be simply accomplished by using whole number range and specifying the values, as it would be impossible to have an entry that was not 4 digits using whole numbers only. The requirements however specifically require a custom formula, which I have been trying and failing with for the past hour and a half. PLEASE HELP!!

 

[FDibbins]

I just tried both of them. They allow me to enter "999" which should prompt an error message if the rule was operating. Since I am using the cell range of J19:J23 I tried modifying the formula to =AND(J19:J23>=1000,J19:J23<=6900,LEN(J19:J23)=4 but I must have made a mistake because it will not accept the formula.

You need to highlight the range you want to apply the DV too, 1st, then use this...
=AND(J19>=1000,J19<=6900,LEN(J19)=4)
use a single-cell ref, not a range

 

[Aladin Akyurek]

I just tried both of them. They allow me to enter "999" which should prompt an error message if the rule was operating. Since I am using the cell range of J19:J23 I tried modifying the formula to =AND(J19:J23>=1000,J19:J23<=6900,LEN(J19:J23)=4 but I must have made a mistake because it will not accept the formula.

If the entries must be whole numbers...

1. Select J19.
2. Acivate Data | Data Validation.
3. Choose Custom.
4. Enter the formula:

=AND(A1>=1000,A1<=6900,A1=TRUNC(A1))
Do the same for each cell of interest.

 

[Mbourque94]

I must have applied something incorrectly, I just cant figure out where the error is. I keep trying to modify the formula and test, but it either does not accept the formula or will accept any data entry/ deny all entries.

 

[FDibbins]

Liker I said above, use single-cell refs, not ranges in the formula

 

[Mbourque94]

Okay so this made good progress. I did exactly what you said above using =AND(J19>=1000,J19<=6900,LEN(J19)=4), but it will deny 6999. If I enter 6900 it will work, but 6950, 6990, and other variations above 6900 all fail.

 

[Mbourque94]

Okay so this made good progress. I did exactly what you said above using =AND(J19>=1000,J19<=6900,LEN(J19)=4), but it will deny 6999. If I enter 6900 it will work, but 6950, 6990, and other variations above 6900 all fail.

Reading the formula back to myself i realized that it contained a max of 6900 and that was the issue. Now it works! Thank you so much!

 

[FDibbins]

Okay so this made good progress. I did exactly what you said above using =AND(J19>=1000,J19<=6900,LEN(J19)=4), but it will deny 6999. If I enter 6900 it will work, but 6950, 6990, and other variations above 6900 all fail.

Your requirement - IF I read it correctly, was

within the range of 1000 and 6900

anything outside that will fail. If you need to include a higher value, adjust the >=6900 as needed