0806 Data Validation - Custom w. Formula Logic

2 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  A$195.60
You save:  A$55.89
List Price:  ৳11,872.36
You save:  ৳3,392.34
List Price:  CA$187.37
You save:  CA$53.54
CHF 91.26
List Price:  CHF 127.77
You save:  CHF 36.51
List Price:  kr884.75
You save:  kr252.80
List Price:  €118.74
You save:  €33.93
List Price:  £107.27
You save:  £30.65
List Price:  HK$1,084.99
You save:  HK$310.02
List Price:  ₹10,502.85
You save:  ₹3,001.03
List Price:  RM586.20
You save:  RM167.50
List Price:  ₦52,986.21
You save:  ₦15,140
List Price:  kr1,265.84
You save:  kr361.69
List Price:  NZ$211.96
You save:  NZ$60.56
List Price:  ₱6,870.27
You save:  ₱1,963.07
List Price:  ₨23,504.32
You save:  ₨6,716
List Price:  S$192.17
You save:  S$54.91
List Price:  ฿4,370.86
You save:  ฿1,248.90
List Price:  ₺1,021.10
You save:  ₺291.76
List Price:  R2,469.09
You save:  R705.50
Already have an account? Log In


Hi there, amongst all the data validation rules, this one, which I'm going to discuss is going to be the most interesting one, why? Because you can include formula based logic inside the data validation. And let me give a quick example. Take for instance column, he currently has certain sales allow that we optimize the window. Yeah. And I wish that in column F, whatever discount that I will be putting in, it should not exceed 50% of sales amount, it should be up to 50% of the sales amount.

Now, how do I bring that in this find out, I choose the entire group of sales, I go to data, I go to validation, I click on Data Validation. Then within that, let me put allow custom validation. Okay, it says formula. Now, I'm going to write a formula as if I'm writing this for the first step itself. So equal to This cell, notice I have not put dollars. Why?

Because I want the logic to flow to the next cell vertically to. So this cell is less than or equal to 50% multiplied with the previous cell. As I press OK, it says whatever value is going to give, it should be up to 50% of the previous sell, which is the sales amount. As I press OK, let me test this out. I put 6000 into Aha, this is the error that I'm getting it right now. If I put 5000 perfect, what if I put 5000 into Yes, I'm getting the same error.

So this is how you can prepare a custom a formula based custom data validation, which will allow you to give the formula and that formula will decide how a user may input the data.

Sign Up