Data validation x2

[The Grim Discovery]

People, please help

Cells A1:A5 have data validation allowing a drop down selection of one of: 'yes', 'no' and 'not sure'.
This works fine.

But I need to add in an extra validation in cell A5 whereby if the selection in A4 is either 'No', or 'not sure', then no entry can be made into cell A5. I don't want people to be able to select an answer or type into the cell.

How can I apply what seems to be two lots of data validation into one cell? Or is there a workaround that preferably doesn't require VBA.

Thanks in advance

 

[Gerald Higgins]

If you can be absolutely certain that A4 is populated with something, and definitely not blank, then try this . . .

=OR(AND(A4="YES",A5="YES"),AND(A4="YES",A5="NO"),AND(A4="YES",A5="NOT SURE"))

This will allow any of the three values to be input into A5, but ONLY if A4 contains "YES".
If A4 contains "NO", or for that matter any other entry, nothing can be input to A5.

Two problems with this -
1) If A4 is blank, it will allow any value to be input to A5
2) The options for A5 don't appear as a drop down box.

 

[L. Howard]

I don't know of a non vba solution but if one does not pop up, this is pretty easy to use.

Copy this into the sheet module of the sheet with the drop downs. Then select No or Not Sure in A4, try to enter something in A5.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target <> Range("A5") Then Exit Sub
 
 Dim noE$
 
 noE = [A4]
 
 Application.EnableEvents = False
 
 If Target.Offset(-1, 0) = "Not Sure" Or Target.Offset(-1, 0) = "No" Then
 
   MsgBox "No entry allowed in " & """A5""" & " with entry of " _
                                 & """" & noE & """" & " In " _
                                 & """A4"" " & "", vbOKOnly + vbCritical
   [A5].ClearContents
   
 End If
 
 Application.EnableEvents = True
End Sub

 

[Murphy123]

People, please help

Cells A1:A5 have data validation allowing a drop down selection of one of: 'yes', 'no' and 'not sure'.
This works fine.

But I need to add in an extra validation in cell A5 whereby if the selection in A4 is either 'No', or 'not sure', then no entry can be made into cell A5. I don't want people to be able to select an answer or type into the cell.

How can I apply what seems to be two lots of data validation into one cell? Or is there a workaround that preferably doesn't require VBA.

Thanks in advance

Hi

It's a bit of a codge but here goes
define two ranges C1:C3 and D1
name them listyes and listno, let cell
c1= yes c2= no and c3= not sure ,leave cell d1 empty, you can put these ranges anywhere even on a different sheet.
Edit the data validation rule for cell a5
in the allow settings choose > list and in
source type > =IF(OR(A4="no",A4="not sure"),listno,listyes)

You can simulate the greying out of cell a5 by using conditioal formatting with ccursor in cell a5 select conditional formatting and select formula type in > =OR(A4="no",A4="not sure") and make the background and font colour light grey.

Regards
Murphy123

 

[The Grim Discovery]

Thanks for sharing your time and knowledge - appreciatos.