Data Validation Query
Posted by JAF on July 18, 2000 8:02 AM
I need to restrict an entry in a specific cell dependent on the values in 2 other cells as follows:
If cell B2 is "Y" and Cell E2 is "Perm" then the only entry that can be allowed is "No".
I suppose that I could do a workbookbefore close macro to check the range and correct any invalid entries, but I would prefer to do it using the Custom option under Data Validation if possible because the file is quite large and is opened and closed several times each day.
I've been playing around with the Data Validation formula, but can't get it to work. Any suggestions?
JAF
Posted by JAF on July 18, 0100 8:11 AM
If you're interested the formula should have been as follows:
=if(and(B2="Y",E2="Perm"),D2="No")
JAF
Posted by JAF on July 18, 0100 8:30 AM
Oops - Not Working. Still Need Help
That'll teach me to be so cocky.
That little Data Validation formula doesn't work in all circumstances.
Entries for the call are invalid if the formula returns a value of FALSE, but either cell can contain different values. What I want to do is to force a value of "No", but only when B2="Y" and E2="Perm"
Posted by Ryan on July 18, 0100 3:24 PM
Re: Oops - Not Working. Still Need Help
JAF,
I don't know how to do it with a formula but this is some code that will prompt the user that the entry is invalid and the proper entry is "No" and asks if they wish to accept that answer. If they click no it clears the cell and selects it for them to redo. Hope it does what you need. This goes in a worksheet module for whatever worksheet the cell is on. I have the code set to cell A1 for the answer NO. You need to change it to whatever cell it is that No goes in.
Ryan
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range
Set VRange = Range("A1") 'Change this to whatever cell you need
Application.ScreenUpdating = False
If Union(Target, VRange).Address = VRange.Address Then
Application.EnableEvents = False
If Range("B2").Value = "Y" And Range("E2").Value = "Perm" _
And Target.Value <> "No" Then
If MsgBox("The valid entry for this cell is 'No'. " & vbCrLf & _
"Do you want to accept this?", vbYesNo + vbCritical, "Invalid Entry") = vbYes Then
Target.Value = "No"
Else
Target.Value = ""
Target.Select
End If
End If
Application.EnableEvents = True
End If
Application.ScreenUpdating = True
End Sub
Posted by Ada on July 18, 0100 7:15 PM
Re: Oops - Not Working. Still Need Help
JAF
You were 99% there. Try :-
=IF(AND(B2="Y",E2="Perm"),"No","")
Ada
Posted by Ada on July 18, 0100 7:29 PM
Correction
Sorry. Didn't read your message properly.
I don't think the formula I gave can be used in Data Validation.
Ada
Posted by Ada on July 18, 0100 7:53 PM
This is the one (I hope)
I think this one works(I hope) :-
=IF(AND(B2="Y",E2="Perm"),D2="No","D2")
Ada
