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

Report •


#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
✔ Best Answer
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 Highlight
the data and warn the user.

See if this works for you:

This is for Excel 2007

There are Three separate Rules,

First Formula:

1) Select your cell or Range of Cells, A1:B1
2) On the ribbon click Conditional Formatting
3) 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 button
7) Select the Fill Tab
8) Select Red color
9) Click OK
10) Click OK

Second formula:

1) Select your cell or Range of Cells, should be the same as above.
2) On the ribbon click Conditional Formatting
3) 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 button
7) Select the Fill Tab
8) Select Red color
9) Click OK
10) Click OK

Third Formula:

1) Select your cell or Range of Cells, should be the same as above
2) On the ribbon click Conditional Formatting
3) 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 button
7) Select the Fill Tab
8) Select Red color
9) Click OK
10) Click OK

If 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

http://www.skeptic.com/


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 Formula
and add an Error Message,

AND

Take the same formula, with a slight modification, and use it in
Conditional Formatting to turn the Cell Red.

1) Select your cell or Range of Cells, A1:B1
2) On the ribbon click Conditional Formatting
3) 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 button
7) Select the Fill Tab
8) Select Red color
9) Click OK
10) Click OK

I 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?

MIKE

http://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 •

Ask Question