Data Validation - Two Questions

[FatalLordes]

Okay, I think this SHOULD be simple but I can't get my head around it.

Here is a sample table:

	A	B	C	D
1	Yes	No	Yes	No
2	No	Yes	Yes	No

So I've worked out how to get a drop down list allowing Yes or No, but my first question is how to NOT allow no answer. For example, the cell MUST contain either a Yes or No or you can't exit cell.

My second question is, using the sample table above, if an answer in a cell for Column D equals "No", then change whatever was entered on the same row but in Column A to "Yes", regardless if it was a Yes or No in there. For example, in Row 2, because No was entered in Column D, then A2 would be changed to Yes.

Hoping someone can help on either or even both questions Thank you in advance

 

[Sektor]

1) To make sure you don't have any other values than "Yes" or "No", you need to setup error message on "Error Alert" tab:

2) To make sure the cell mustn't be empty, you need a bit of VBA, since Data Validation doesn't raise error if you clear a cell. In the worksheet module you need to handle "Worksheet_Change" event:

Private strVal As String

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect([A1:D2], Target) Is Nothing Then Exit Sub
  If Target = vbNullString Then
    MsgBox "Cell cannot be empty!", vbCritical
    '// restore value
    Application.EnableEvents = False '//disable recursive events
    Target.Value = strVal
    Application.EnableEvents = True
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  '// track selection
  If Not Intersect([A1:D2], Target) Is Nothing Then
    strVal = Target.Value
  End If
End Sub

 

[FatalLordes]

1) To make sure you don't have any other values than "Yes" or "No", you need to setup error message on "Error Alert" tab:
View attachment 114078View attachment 114077
2) To make sure the cell mustn't be empty, you need a bit of VBA, since Data Validation doesn't raise error if you clear a cell. In the worksheet module you need to handle "Worksheet_Change" event:

Private strVal As String

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect([A1:D2], Target) Is Nothing Then Exit Sub
  If Target = vbNullString Then
    MsgBox "Cell cannot be empty!", vbCritical
    '// restore value
    Application.EnableEvents = False '//disable recursive events
    Target.Value = strVal
    Application.EnableEvents = True
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  '// track selection
  If Not Intersect([A1:D2], Target) Is Nothing Then
    strVal = Target.Value
  End If
End Sub

Thanks for this. I'll give this a whirl in a couple of days time and let you know how I go!

 

[FatalLordes]

1) To make sure you don't have any other values than "Yes" or "No", you need to setup error message on "Error Alert" tab:
View attachment 114078View attachment 114077
2) To make sure the cell mustn't be empty, you need a bit of VBA, since Data Validation doesn't raise error if you clear a cell. In the worksheet module you need to handle "Worksheet_Change" event:

Private strVal As String

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect([A1:D2], Target) Is Nothing Then Exit Sub
  If Target = vbNullString Then
    MsgBox "Cell cannot be empty!", vbCritical
    '// restore value
    Application.EnableEvents = False '//disable recursive events
    Target.Value = strVal
    Application.EnableEvents = True
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  '// track selection
  If Not Intersect([A1:D2], Target) Is Nothing Then
    strVal = Target.Value
  End If
End Sub

Hi

I tried doing the error validation in response to my first question, but it is not working. I'm using Excel on the Web (within OneDrive), so not sure if there are limitations to that? Basically, I tried with the "Ignore Blank" ticked and not ticked, and completed Error as shown, but it still lets me tab past a blank cell:

 

[Sektor]

Oh, you should've mentioned Web version beforehand. Of course, in Web you cannot use VBA code.

 

[FatalLordes]

Oh, you should've mentioned Web version beforehand. Of course, in Web you cannot use VBA code.

Yeah sorry about that. Ok, so no options then to solve questions 1 or 2 when using the web version?