Forum Discussion
Help with Data Validation of Min and Max with Overlap Yes/No
Hello,
I have difficulty creating a data validation set. How do I create one column that automatically answers "yes" or "no" (I am assuming something similar to True/False) by itself using the values from the previous 2 columns with a min/max? I have attached an image below to follow with and explain more thoroughly.
Looking at the image:
I need Column P to answer Yes/No using the values from Columns N and O for each row with a value.
In each row, Column N values need a minimum of 400, and Column M values need a maximum of 500. (This overlap data validation answer would be NO in Column P). If one or both of the Columns N and O value do NOT succeed the min/max, the overlap data validation answer would be YES in Column P)
(#1)For example, in row 8, N8 is 8700 and O8 is 8900. N8 succeeds the 400 minimum, but O8 oversees the 500 maximum. Therefore, the overlap data validation answer for this is YES because even though N8 reached the minimum, O8 went beyond the maximum.
(#2) Hypothetical example: Say if N1 value is 435 and O1 value is 450. This overlap data validation answer will be NO. This is because N1 meets the 400 minimum and O1 also meets the 500 maximum. So there is no overlapping between these values.
The answer will be....
YES: If Column N and/or Column O values do not succeed the 400 minimum and 500 maximum.
NO: If BOTH Columns N and O, succeed the 400 minimum and 500 maximum.
Thank you SO much! Let me know if there are any more details I could give for assistance! I really need help on this assignment ASAP!
10 Replies
Since you've already gone to the trouble of creating the spreadsheet, unless it has proprietary information in it, it would be a LOT easier to help you if you attached the spreadsheet itself. I'm going to suggest a formula that should work, but with the actual, we could test it.
IF(AND(N8>$N$4,O8<$O$4),"Yes","No")
Copy that down to succeeding rows. You can surround it with other conditions for blank rows....
For future reference, though, when you ask for help, you help us help you by posting the sheet itself, not just an image.
Hello, I understand, I was only unable to upload the spreadsheet and share because it has proprietary information in it. Unfornately, I am not sure if a formula to copy for each row would work. This spreadsheet has over 10,000 rows and I would need a format tool instead for it to be time fast and efficient instead of time consuming. Unless you know how to copy the formula from top to bottom of the spreadsheet in a quick manner that'd be appreciated!
I was looking at the "Data Validation" to see what information I could put in to have it spit out Yes/No. Again, I am not sure.
Thank you!
Data Validation is something altogether different. You're looking in this case for a formula to check on the content of two other cells. You've been given, by Riny_van_Eekelen and me, two different (equally functional) formulas.
Data Validation could be used, if this were applicable, though I don't think it is, if you were entering the frequencies for a given station and only wanted to accept entries that met certain criteria. Or it can be used to make sure a name of say a department is on the approved list, thus not only validating that it's on the list but also that it gets entered with the correct spelling, etc. Anyway, different purposes than what you were wanting to do here.
anniehoang Perhaps best demonstrated in the attached example. Adjust the references to your own.
