Dependent Data Validation

[clappl001]

I am trying to limit a cells data to a certain range ie 25 - 100 but I want the range to change depending on what is entered in a different cell.

So if A1 says "yellow", the range allowed in B1 is 25 - 100
If A1 says "blue", the range allowed in B1 is 100 - 200

Thanks for any help.

 

[dreid1011]

Perhaps this:

=OR(AND(A1="Yellow",B1>=25,B1<=100),AND(A1="Blue",B1>=100,B1<=200))

You could store the min/max values in a table if you ever need to change them and just reference those values, though I am not sure if you can make it simpler by using a lookup formula in data validation.

Edit: However, you could use a combination of a lookup table and a lookup formula to pull the min/max values, and then have the data validation reference the pulled values.

 

[dreid1011]

Something like this:

Where B4:C4 is using lookup formulas to pull the Min and Max values from the table in A7:C8 based on the color in A1. Then the data validation is comparing B1 value to B4:C4 values. And the data validation formula is much simpler as well. Note the lookup table must be sorted alphabetically with VLOOKUP. You can use a different lookup formula if you don't want to sort the list.

Book1
	A	B	C
1	Blue	199
2
3		Min	Max
4		100	200
5
6	Lookup Table	Min	Max
7	Blue	100	200
8	Yellow	25	100
Sheet3

Cell Formulas
Range	Formula
B4	B4	=VLOOKUP($A$1,$A$7:$C$8,2)
C4	C4	=VLOOKUP($A$1,$A$7:$C$8,3)

Cells with Data Validation
Cell	Allow	Criteria
B1	Custom	=AND(B1>=B4,B1<=C4)

 

[clappl001]

Thanks I will try these out!

 

[clappl001]

Perhaps this:

=OR(AND(A1="Yellow",B1>=25,B1<=100),AND(A1="Blue",B1>=100,B1<=200))

You could store the min/max values in a table if you ever need to change them and just reference those values, though I am not sure if you can make it simpler by using a lookup formula in data validation.

Edit: However, you could use a combination of a lookup table and a lookup formula to pull the min/max values, and then have the data validation reference the pulled values.

Hopefully last question! Is it possible to now prevent any data from being entered into B1 before something is entered into A1? Thanks again.

 

[dreid1011]

Yes, within the AND() structure, you can add A1<>"" or NOT(ISBLANK(A1)) and uncheck the "Ignore Blanks" check box. Either of those should do the trick.

 

[clappl001]

Yes, within the AND() structure, you can add A1<>"" or NOT(ISBLANK(A1)) and uncheck the "Ignore Blanks" check box. Either of those should do the trick.

This is working almost perfectly!! Heres what I have,

Purchase order template for Mid-Can.xlsx
	C	D	E	F
11	Operation	Size (in)
12		Wide		Height
13	Single Slide	34.00	X
14			X
15			X
16			X
17			X
18			X
19			X
20			X
21			X
22			X
Order Guide

Cells with Data Validation
Cell	Allow	Criteria
F13:F22	Whole number	between 24 and 118.11
C13:C22	List	=Sheet3!$D$2:$D$4
D13	Custom	=OR(AND(NOT(ISBLANK(C13)),C13="Single Slide",D13>=24,D13<=177.19),AND(NOT(ISBLANK(C13)),C13="Double Slide",D13>=48.75,D13<=354.33),AND(NOT(ISBLANK(A1)),C13="Duo Slide",D13>=24,D13<=177.19))
D14	Custom	=OR(AND(C14<>"",C14="Single Slide",D14>=24,D14<=177.19),AND(C14<>"",C14="Double Slide",D14>=48.75,D14<=354.33),AND(C14<>"",C14="Duo Slide",D14>=24,D14<=177.19))
D15:D22	Custom	=OR(AND(C15="Single Slide",D15>=24,D15<=177.19),AND(C15="Double Slide",D15>=48.75,D15<=354.33),AND(C15="Duo Slide",D15>=24,D15<=177.19))

The issue I am facing now is that I can't clear the data that I am inputting into D13. Even when I remove my selection from C13, D13 is unable to accept not having a value.

 

[dreid1011]

You should be able to add a condition to your outermost OR() structure like this:

=OR(D13="", ...... )

 

[clappl001]

Now I am faced with an issue where if I put C13 to Double Slide, and enter a value in D13 that is over the max for Single Slide, and then I go and change C13 to Single Slide, D13 doesn't seem to realize that it is now outside of the new range and does not update. Is there a solution to this?

 

[dreid1011]

Now I am faced with an issue where if I put C13 to Double Slide, and enter a value in D13 that is over the max for Single Slide, and then I go and change C13 to Single Slide, D13 doesn't seem to realize that it is now outside of the new range and does not update. Is there a solution to this?

As I think about that, it might be a limitation of data validation because you're not changing the value in the cell with the DV rules. It might be worth looking into some VBA to trigger on worksheet change events.