Hi, I'd like to set up data validation with three criteria checks.
The scenario is -
This is what I have so far which does not work in the data validation feature but does if I enter it in a cell and change the values in the sheet so I'm fairly sure the logic is right -
Please Login or Register to view this content.
Formula:
Please Login or Register to view this content.
You can't do that with data validation. Just use a formula in the cell where you want the message to appear.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
That's a shame I'll have to think about how to handle it. Thank you.
If you share a sample workbook and explain EXACTLY what you want, we might be able to offer a workaround.
Ok cool thanks, should I post a new question or add to this one?
Add to this one and remove the SOLVED tag for the time being.
Hi, I've attached a sample sheet -
I've got the validation test in H3 showing a pass -Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
Added a couple of other examples showing failures
OK, so DV for cell E4 and F4 should be:
=IF($C4="W",$E4>$F4,IF($C4="L",$E4<$F4,$E4=$F4))
Last edited by AliGW; 08-21-2023 at 11:00 AM.
Oh nice that works, thank you!
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
Thanks, done, adding rep has been denied I need to spread the love apparently.
Try
=IF(AND(C4="W",E4>F4),"Pass",IF(AND(C4="L",E4<F4),"Pass",IF(AND(C4="D",E4=F4),"Pass","Fail")))[/FORMULA]Formula:Please Login or Register to view this content.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Never mind - that happens!adding rep has been denied I need to spread the love apparently.
Thanks John, that seems to be a shorter/cleaner version of what I had, but doesn't work with the DV, unless I'm making an error.Originally Posted by JohnTopley
Try
=IF(AND(C4="W",E4>F4),"Pass",IF(AND(C4="L",E4<F4),"Pass",IF(AND(C4="D",E4=F4),"Pass","Fail")))[/FORMULA]Formula:Please Login or Register to view this content.
DV will NOT return messages derived by a formula - that was the point of post #2.
You can have what I suggested as DV AND a message appearing in a cell to the right derived by a formula, but DV cannot generate that message for you. The DV message has to be done via the dialog box and needs to cover all eventualities - it's a one-stop shop.
To be honest, I am not even sure what the objective off the "DV" is?
You could derive the W,D,L from the For/Against columns rather use a drop down.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks