Custom Validation Formula

M

Mbourque94

New Member
Joined
Jun 11, 2017
Messages
7
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!!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Hi welcome to the board :)

TRy this...
=AND(A1>=1000,A1<=6900,LEN(A1)=4)
The requirements however specifically require a custom formula
Click to expand...
and what requirements would they be?
 
Last edited:
Upvote 0
Aladin, any decimal value with 4 digits will be less than 1000 and any decimal > 1000 with a decimal will be > 6900, that's why I just used LEN() :)
 
Upvote 0
Thanks for the quick response. All that is stated for specific instructions is " Create a custom data validation in cells J19:J23 that accept entry only if four digits in length and in a range from 1000 to 6999."
 
Upvote 0
No it isn't homework. I start a new job next week so I have been working on excel assignment from an accounting course I took last year to be better prepared for the amount I will rely on excel for the job.
 
Upvote 0
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.
 
Upvote 0
With a test for LEN()=4, you will not be able to enter 999, perhaps you applied it incorrectly?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
How to Run a AppleScript from VBA using AppleScriptTask
Replies
2
Views
519
Fluff
Fluff
N
  • Question
Formula in a Custom Data Validation is not working when a criteria cell is Blank
Replies
1
Views
634
narendra
N
T
  • Solved
Can i get a table range in a conditional format to expand with table expansion?
Replies
7
Views
201
Micron
M
M
  • Question
Add in VBA Code to check zip code against pre-determined list
Replies
0
Views
156
mikefisher
M
N
  • Solved
Formula to change font color in part of cell only
Replies
2
Views
340
Nathan Asius
N

Forum statistics

Threads
1,214,971
Messages
6,122,521
Members
449,088
Latest member
RandomExceller01

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top