Data validation based on value in another cell

[chasidar]

Hi, I'm using Excel365. I searched the forum and didn't find a solution to my question.

I have 2 columns with data validation. In column E I choose yes or no from a list. In column D I have to choose who signed a form from a list of three options.

I want to have an option that if I choose No in column E, then N/A is automatically entered into column D. If yes is chosen in column E, I want to choose a value from the list in column D.

Is this possible?

I tried entering this in my data validation: =IF($E$2="No", "N/A" ,Lists!$A$1:$A$3) but it doesn't enter N/A in the cell if I choose No.

 

[6StringJazzer]

Data validate rules cannot enter a value into a cell. They can only validate the value entered by a user.

In column D, you can have a formula that says

=IF($E2="No","N/A","")

But you will have to remove the data validation, put in the formula, then put the data validation back again. Otherwise the data validation will reject the results of the formula. However, when adding data validation, it does not validate whatever is already in the cell.

Also, if the user ignores the "N/A" and selects an item from the dropdown list, it's going to wipe out the formula, so it will never be N/A again.

The only other way to do this is with VBA.

$scratch.xlsm
	D	E	F	G
1	Signed By	Medicine		Jeanine
2	N/A	No		Kathy
3		Ibuprofen		Bud
4		Morphine
5		Ketamine
6
7
8
Sheet3

Cell Formulas
Range	Formula
D2:D8	D2	=IF(E2="No","N/A","")

Cells with Data Validation
Cell	Allow	Criteria
D2:D8	List	=$G$1:$G$3

 

[chasidar]

Data validate rules cannot enter a value into a cell. They can only validate the value entered by a user.

In column D, you can have a formula that says

=IF($E2="No","N/A","")

But you will have to remove the data validation, put in the formula, then put the data validation back again. Otherwise the data validation will reject the results of the formula. However, when adding data validation, it does not validate whatever is already in the cell.

Also, if the user ignores the "N/A" and selects an item from the dropdown list, it's going to wipe out the formula, so it will never be N/A again.

The only other way to do this is with VBA.

$scratch.xlsm
	D	E	F	G
1	Signed By	Medicine		Jeanine
2	N/A	No		Kathy
3		Ibuprofen		Bud
4		Morphine
5		Ketamine
6
7
8
Sheet3

Cell Formulas
Range	Formula
D2:D8	D2	=IF(E2="No","N/A","")

Cells with Data Validation
Cell	Allow	Criteria
D2:D8	List	=$G$1:$G$3

Thanks. Can you explain how I do this with VBA?

 

[kevin9999]

One possibility with VBA. Put the following code in the sheet code window (right-click the sheet tab name & select View Code - put it in the window that appears on the right of screen)
Assumes you have a sheet called "Lists" and that there are entries on that sheet in the range A1:A3. This code goes in the sheet module of the sheet with the columns D & E you described in your original post.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row > 1 And Target.Column = 5 Then
        On Error GoTo Escape
        Application.EnableEvents = False
        If Target.Value = "No" Then
            With Target.Offset(0, -1)
                .Validation.Delete
                .Value2 = "N/A"
            End With
        ElseIf Target.Value = "Yes" Then
            With Target.Offset(0, -1)
                .Value = ""
                With .Validation
                    .Delete
                    .Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, _
                    Formula1:="=Lists!$A$1:$A$3"
                End With
            .Select
            End With
        End If
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Book1
	D	E
1	Signed By	Yes or No
2	N/A	No
3
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
E2:E3	List	Yes,No

Book1
	D	E
1	Signed By	Yes or No
2	Option 1	Yes
3
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
E2:E3	List	Yes,No
D2	List	=Lists!$A$1:$A$3

 

[chasidar]

Thanks. I get an argument not optional error. I've reviewed the code but I can't see why this error should be returned.

 

[6StringJazzer]

What line of code gives you the error?

 

[kevin9999]

As per Jeff's question, run the following code (error handling removed) and let us know what line the error occurs on. It would also help if you could share your file via Google Drive, Dropbox or similar file sharing platform.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row > 1 And Target.Column = 5 Then
        Application.EnableEvents = False
        If Target.Value = "No" Then
            With Target.Offset(0, -1)
                .Validation.Delete
                .Value2 = "N/A"
            End With
        ElseIf Target.Value = "Yes" Then
            With Target.Offset(0, -1)
                .Value = ""
                With .Validation
                    .Delete
                    .Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, _
                    Formula1:="=Lists!$A$1:$A$3"
                End With
            .Select
            End With
        End If
    End If
End Sub