Data Validation Dilema

[Bedford]

Needing a solution, a formula in data validation; =OR(D13="",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))

Is working well for us. The data validation is in D13, and is dependent on data in C13. C13 has a drop down of a list of 3; Single Slide, Duo and Double Slide. Single Slide and Duo are using the same data validation of min/max, the Double Slide slightly different. The cell C13 is selected first, then the data is entered in D13, there is an error message if the entry in D13 is not within the min/max criteria that tells it's out of spec. This is working very well.

Dilema; if C13 is selected as Single Slide and correct data is entered in D13 without error, but the user returns to C13 and changes to Double Slide the data in D13 is now out of spec. When this last change is made I'm looking to have an error appear if C13 changes from Single Slide to Double Slide after D13 has seen an entry?

I hope I'm explaining this correctly, I'm not sure if a XI2bb mini sheet would illustrate what I'm hoping to acheive.
Thanks.

 

[KRice]

What is the purpose of cell A1, which is used only when Duo Slide is selected? The formula presented can be trimmed down some, as the NOT(ISBLANK(C13)) construction isn't necessary...the expression already includes checks that C13 must be either Single Slide or Double Slide as partial conditions for evaluating as TRUE...and both are, by definition, not blank. However, this still doesn't address the issue you've described. Could you clarify...you are using the OR/AND expression in Data Validation for cell D13, and you use that DV to flag whether the value typed in D13 satisfies certain criteria? How are you conveying whether data in D13 are in spec? Does nothing happen if the value is okay, and if not, does a message appear?

 

[KRice]

I think there are two potential approaches to consider. Data Validation evaluates when information is entered into the cell. In this sense, it is not dynamic to address the scenario you've encountered. So one approach is to not use Data Validation, but rather use a variation of the formula in another cell (here, in E13) to evaluate whether the data entered into D13 satisfies specifications. This is dynamic and responds to changes in C13 and D13. I would prefer this approach because it accepts whatever value the user inputs and then performs the assessment. Otherwise, the user would have to modify the input in order for the cell to accept it (and in many cases, this would not be acceptable).

Book1
	A	B	C	D	E
1	a1
13			Double Slide	24	Out of spec
Sheet2

Cell Formulas
Range	Formula
E13	E13	=IF( OR( D13="", AND( C13="Single Slide", D13>=24, D13<=177.19), AND( C13="Double Slide", D13>=48.75, D13<=354.33), AND( C13="Duo Slide", D13>=24, D13<=177.19, NOT(ISBLANK(A1)) )), "In spec","Out of spec")

Cells with Data Validation
Cell	Allow	Criteria
C13	List	Single Slide,Duo Slide,Double Slide

The other option would be to use VBA to perform the specification evaluation anytime values in C13:D13 change.

 

[Bedford]

Is there a way to have E13 appear blank when there isn't anything in C13 or D13? Currently when both are blank it E13 contains "In spec"

Thanks.

 

[KRice]

Sure...try something like this, which nests two IF statements. The first logical check determines if either C13 or D13, or both, are blank. If so, then a blank is returned. And if not (meaning C13 and D13 both are not blank), then the 2nd IF statement is executed. That 2nd IF then applies the spec tests, and Data Validation in C13 enforces the entry of only allowable choices (single, double, duo).

MrExcel_20231113.xlsx
	A	B	C	D	E
1	a1
13			Duo Slide	25	In spec
Sheet3

Cell Formulas
Range	Formula
E13	E13	=IF(OR(C13="",D13=""),"", IF(OR( AND( C13="Single Slide", D13>=24, D13<=177.19), AND( C13="Double Slide", D13>=48.75, D13<=354.33), AND( C13="Duo Slide", D13>=24, D13<=177.19, NOT(ISBLANK(A1)) )), "In spec","Out of spec"))

Cells with Data Validation
Cell	Allow	Criteria
C13	List	Single Slide,Duo Slide,Double Slide

 

[Bedford]

This is exactly what we were hoping for, thank you very much for your help!

 

[KRice]

You're welcome...I'm happy to help. I'm still not clear on what role NOT(ISBLANK(A1)) plays, but I'm assuming that is needed for the Duo evaluation.

 

[dreid1011]

You're welcome...I'm happy to help. I'm still not clear on what role NOT(ISBLANK(A1)) plays, but I'm assuming that is needed for the Duo evaluation.

That was originally part of the data validation I supplied in another thread for the same problem.

 

[KRice]

I appreciate the explanation. I tracked down that other thread and see that we reached the same recommendations: due to the limitation with Data Validation (it will only evaluate upon data entry into its cell, as opposed to evaluating when a dependent cell changes)…either use a formula that considers all relevant cells or use a VBA solution involving change events.
Dependent Data Validation