# Solved IF statement with a data validation

October 8, 2012 at 05:34:11
Specs: Windows
 I am trying to do an if statement to include data validation values (NOT validation list). So if cell A1 says "High" then in cell B1 you can ONLY entry and specific values i.e: below 1.5 and if A1 says "low" then you can only entry values in cell B1 between 1.5 and 2.2 and if A1 says "medium" then in cell B1 you must entry values higher than 3. I see many examples with Validation List but this is not the case. Can anyone help?Thank uou

See More: IF statement with a data validation

#1
October 8, 2012 at 07:28:04
 Do your allowed values have any limits? In other words, when you say "below 1.5", is -146,876.89 a valid entry?If not, and you want to limit the allowable entries to 0, .1, .2, .3, ...1.3, 1.4 etc. then you can create a create a list of allowed values for each of your three criteria and then use the technique found here:http://www.contextures.com/xlDatava...The user would only be able to a choose a value from whatever list is presented and that list would be based on the value in A1.However, if there is an infinite number of allowable values within your ranges, then obviously that won't work.Check it out and let us know.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
October 8, 2012 at 10:53:39
 thats exactly what I try to avoid, make a list with all the possible values. Can be many!! It would be a very long list. Is possible to do it without that list??Thanks!!

Report •

#3
October 8, 2012 at 12:10:22
 Try this Data Validation Custom Formula:=OR(AND(A1="High",B1<1.5),AND(A1="Low",B1>1.5,B1<2.2),AND(A1="Medium",B1>3))Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

#4
October 8, 2012 at 12:10:50
 This does not prevent the data from being entered,but using Conditional Formatting, you can Highlightthe data and warn the user.See if this works for you:This is for Excel 2007There are Three separate Rules,First Formula:1) Select your cell or Range of Cells, A1:B12) On the ribbon click Conditional Formatting3) Click on New Rules, it’s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =AND(\$A1="High",\$B1>1.5)6) Click on the Format button7) Select the Fill Tab8) Select Red color9) Click OK10) Click OKSecond formula:1) Select your cell or Range of Cells, should be the same as above.2) On the ribbon click Conditional Formatting3) Click on New Rules, it’s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =AND(\$A1="Medium",\$B1<3)6) Click on the Format button7) Select the Fill Tab8) Select Red color9) Click OK10) Click OKThird Formula:1) Select your cell or Range of Cells, should be the same as above2) On the ribbon click Conditional Formatting3) Click on New Rules, it’s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =AND(\$A1="Low",OR(\$B1<=1.5,\$B1>=2.2))6) Click on the Format button7) Select the Fill Tab8) Select Red color9) Click OK10) Click OKIf the data is in error, both cells A1 & B1 turn Red.You may need to tweek the values a bit to get the range of values the way you want.MIKE

Report •

#5
October 8, 2012 at 13:43:31
 You can actually have the best of both worlds.Use DerbyDad03's formula in Data Validation, Custom Formulaand add an Error Message,ANDTake the same formula, with a slight modification, and use it inConditional Formatting to turn the Cell Red.1) Select your cell or Range of Cells, A1:B12) On the ribbon click Conditional Formatting3) Click on New Rules, it’s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =NOT(OR(AND(\$A1="High",\$B1<1.5),AND(\$A1="Low",\$B1>1.5,\$B1<2.2),AND(\$A1="Medium",\$B1>3)))6) Click on the Format button7) Select the Fill Tab8) Select Red color9) Click OK10) Click OKI haven't tested it extensively but it seems to work.Your only problem is what happens with the value entered is exactly 1.5 or if the value entered is greater then 2.2 but less than 3?MIKEhttp://www.skeptic.com/

Report •

#6
October 9, 2012 at 02:39:15
 Thanks so much Derby! It works!!

Report •

#7
October 9, 2012 at 02:39:54
 Thanks MIKE!! This is simply lovely! Perfect!

Report •