0806 Data Validation - Custom w. Formula Logic

Advanced Excel Crash Course Section 8: Data Input
2 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€92.72
List Price:  €129.81
You save:  €37.09
£79.26
List Price:  £110.97
You save:  £31.71
CA$135.49
List Price:  CA$189.69
You save:  CA$54.20
A$153.49
List Price:  A$214.89
You save:  A$61.40
S$134.92
List Price:  S$188.90
You save:  S$53.97
HK$782.50
List Price:  HK$1,095.53
You save:  HK$313.03
CHF 90.16
List Price:  CHF 126.23
You save:  CHF 36.07
NOK kr1,085.06
List Price:  NOK kr1,519.13
You save:  NOK kr434.06
DKK kr691.90
List Price:  DKK kr968.69
You save:  DKK kr276.78
NZ$167.41
List Price:  NZ$234.39
You save:  NZ$66.97
د.إ367.19
List Price:  د.إ514.08
You save:  د.إ146.89
৳10,980.73
List Price:  ৳15,373.46
You save:  ৳4,392.73
₹8,335.04
List Price:  ₹11,669.39
You save:  ₹3,334.35
RM472.40
List Price:  RM661.38
You save:  RM188.98
₦139,532.04
List Price:  ₦195,350.44
You save:  ₦55,818.40
₨27,813.53
List Price:  ₨38,940.06
You save:  ₨11,126.52
฿3,638.31
List Price:  ฿5,093.78
You save:  ฿1,455.47
₺3,237.16
List Price:  ₺4,532.16
You save:  ₺1,294.99
B$501.53
List Price:  B$702.17
You save:  B$200.63
R1,888.66
List Price:  R2,644.21
You save:  R755.54
Лв180.76
List Price:  Лв253.07
You save:  Лв72.31
₩134,741.70
List Price:  ₩188,643.77
You save:  ₩53,902.07
₪367.71
List Price:  ₪514.81
You save:  ₪147.10
₱5,617.33
List Price:  ₱7,864.49
You save:  ₱2,247.16
¥15,132.45
List Price:  ¥21,186.03
You save:  ¥6,053.58
MX$1,658.17
List Price:  MX$2,321.51
You save:  MX$663.33
QR364.88
List Price:  QR510.84
You save:  QR145.96
P1,378.06
List Price:  P1,929.34
You save:  P551.28
KSh13,256.67
List Price:  KSh18,559.87
You save:  KSh5,303.20
E£4,744.14
List Price:  E£6,641.99
You save:  E£1,897.84
ብር5,687.29
List Price:  ብር7,962.44
You save:  ብር2,275.14
Kz83,433.85
List Price:  Kz116,810.73
You save:  Kz33,376.88
CLP$98,290.17
List Price:  CLP$137,610.17
You save:  CLP$39,320
CN¥722.08
List Price:  CN¥1,010.95
You save:  CN¥288.86
RD$5,924.54
List Price:  RD$8,294.60
You save:  RD$2,370.05
DA13,479.97
List Price:  DA18,872.49
You save:  DA5,392.52
FJ$225.66
List Price:  FJ$315.94
You save:  FJ$90.27
Q780.13
List Price:  Q1,092.22
You save:  Q312.08
GY$20,949.30
List Price:  GY$29,329.86
You save:  GY$8,380.56
ISK kr13,941.60
List Price:  ISK kr19,518.80
You save:  ISK kr5,577.20
DH1,013.74
List Price:  DH1,419.27
You save:  DH405.53
L1,764.37
List Price:  L2,470.19
You save:  L705.82
ден5,702.11
List Price:  ден7,983.18
You save:  ден2,281.07
MOP$806.29
List Price:  MOP$1,128.85
You save:  MOP$322.55
N$1,909.24
List Price:  N$2,673.01
You save:  N$763.77
C$3,682.20
List Price:  C$5,155.22
You save:  C$1,473.02
रु13,347.49
List Price:  रु18,687.02
You save:  रु5,339.53
S/372.08
List Price:  S/520.93
You save:  S/148.85
K382.87
List Price:  K536.04
You save:  K153.16
SAR375.15
List Price:  SAR525.22
You save:  SAR150.07
ZK2,481.24
List Price:  ZK3,473.83
You save:  ZK992.59
L460.94
List Price:  L645.33
You save:  L184.39
Kč2,346.52
List Price:  Kč3,285.22
You save:  Kč938.70
Ft36,532.34
List Price:  Ft51,146.74
You save:  Ft14,614.40
SEK kr1,069.05
List Price:  SEK kr1,496.71
You save:  SEK kr427.66
ARS$85,802.56
List Price:  ARS$120,127.02
You save:  ARS$34,324.45
Bs691.35
List Price:  Bs967.92
You save:  Bs276.56
COP$386,901.39
List Price:  COP$541,677.43
You save:  COP$154,776.03
₡50,320.66
List Price:  ₡70,450.94
You save:  ₡20,130.27
L2,469.73
List Price:  L3,457.73
You save:  L987.99
₲739,686.52
List Price:  ₲1,035,590.72
You save:  ₲295,904.20
$U3,756.16
List Price:  $U5,258.77
You save:  $U1,502.61
zł399
List Price:  zł558.62
You save:  zł159.61
Already have an account? Log In

Transcript

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

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.