Data Validation with Multiple Conditions

[jmoore19023]

Hey,

I'm pretty new to excel and am trying to figure out a way to force users to enter data under certain conditions. I have a form where they enter a number every hour. Based on what that number is, they may have to provide additional details.

Currently I have it to where they cannot skip the putting in the number for the previous hour using the custom selection in data validation and the formula given: =(COUNTIF(C6,"")=0). This goes down several rows checking each prior cell for a value ensuring that that user has entered some value.

Now where I'm getting hung up at is, I need the data validation to ensure that when a target is not met(which I have a cell that figures that), that a reason must be provided. Is this possible? And if so, how would I set the formula up in data validation so that both criterias are met.

1.) Data in above cell must not be "".
2.) If value in above cell does not meet target, a value must be present in cell K (and L if possible) before next hour's value can be entered.

Thanks in advance and sorry if I explained this poorly.

 

[dreid1011]

Welcome to the forum. I'm not sure which cell you are wanting to put the validation in, so I used G7 with G6 as the example. Update the values as needed for your target criteria.

=AND(ABS(G6)>0,OR(AND(G6<-1000,K6<>"",L6<>""),G6>=-1000))

Use the above formula with the following settings:

 

[jmoore19023]

Welcome to the forum. I'm not sure which cell you are wanting to put the validation in, so I used G7 with G6 as the example. Update the values as needed for your target criteria.

=AND(ABS(G6)>0,OR(AND(G6<-1000,K6<>"",L6<>""),G6>=-1000))

Use the above formula with the following settings:
View attachment 112606

Hey Dreid,

Thank you for getting back to me. I'm going to play with what you provided and try to make it work.

Also here's the extra context that I forgot to add previously, just in case that may change anything.

C6 = Hourly Input
F6 = Value above/below target.

When a user enters a value in C7,

1.) C6 must have a value present. (FYI, there are cases where a 0 can be entered, although rare.)
2.) If F6 is negative(meaning they missed target), that means K6 & L6 must have values.

Thank you again! I think I can work with what you provided, but I'll reach out if I start to struggle again.

 

[jmoore19023]

Just a quick follow up, I already managed to change the formula to work exactly as I wanted.

Again, I appreciate the assistance. I spent alot of time googling and rubbing my temples trying to figure this out haha

 

[dreid1011]

Hey Dreid,

Thank you for getting back to me. I'm going to play with what you provided and try to make it work.

Also here's the extra context that I forgot to add previously, just in case that may change anything.

C6 = Hourly Input
F6 = Value above/below target.

When a user enters a value in C7,

1.) C6 must have a value present. (FYI, there are cases where a 0 can be entered, although rare.)
2.) If F6 is negative(meaning they missed target), that means K6 & L6 must have values.

Thank you again! I think I can work with what you provided, but I'll reach out if I start to struggle again.

Just a quick follow up, I already managed to change the formula to work exactly as I wanted.

Again, I appreciate the assistance. I spent alot of time googling and rubbing my temples trying to figure this out haha

You're welcome, and glad you got it working.