Create a data validation in a cell based on the value in another cell

excel data validation list based on the value of another
how to restrict value entry/input in a cell based on another cell value in excel
excel data validation formula if statement
combine if statement and data validation
data validation based on another cell google sheets
excel data validation custom multiple criteria
excel drop down list if statement
excel drop down list populates data in another cell

I have 2 columns in a spreadsheet, both are picked from a drop down list.

Col A is "Do you want more information" - Possible Values Yes\No

Col B is "How did you hear about us" - Possible values are Email\Phone\Other

Question: If "No" is chosen in Col A, then Col B's value should be N/A, otherwise the user should be able to choose from the dropdown and pick a value. Using the following formula, I can get N/A to appear, but the "false" option overwrites any available dropdown values or previously selected values. So simply put, if Yes is chosen, let user choose a value in the other cell. If No is chosen, show N/A in the other cell. Thoughts on how I can get this to work?

My current formula that doesn't work (which resides in cell A2):

=IF(A1="No","N/A","Select Value")

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    If Target.Column = 1 Then
        Set Cell = Target.Offset(0, 1)
        If Len(Target.Value) = 0 Then
            Cell.Validation.Delete
            Cell.Value = vbNullString
        Else
            If Target.Value = "Yes" Then
                With Cell.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="=ContactMethod"
                End With
            ElseIf Target.Value = "No" Then
                Cell.Validation.Delete
                Cell.Value = "N/A"
            Else
                MsgBox "Input only Yes or No."
                Target.ClearContents
                Cell.Validation.Delete
            End If
        End If
    End If
End Sub

Screenshot:

If Yes is entered in Column A (note the dropdown arrow):

If No is entered in Column A (note the now missing dropdown arrow):

If anything else is entered in Column A:

In addition, it safely deletes the validation if Column A's value is deleted or changed to No. This way, there's no way to access the dropdown unless Column A is specifically Yes.

Make sure to paste it in the sheet's module (Sheet9 in the screenshot below):

Let us know if this helps.

How to make Excel Data Validation Dependent Lists, Another option is available in versions of Excel that include dynamic array The third is setting up the data validation cells based on the defined names. Alright We need a formula that will retrieve the column values for the  Data Validation based on another cell value I'm trying to create a formula that works such that if the value in cell A11 is A, then the values that can be input in B11, can only be between 0.06 and 0.08 inclusive.

Just for the fun of it, because it is not a complete solution, that will need a litle vba... Firs ComboBox in A1 (Yes/No), in D1:D3 (Email/Phone/Other), in E1 (NA). Write as source for B1 list Data Validation:

=CHOOSE(IF(A1="Yes";1;2);D1:D3;E1)

You need VBA, for, at least, delete the value in B1 when the value in A1 changes, and change it to "NA" if A1=No

11 Awesome Examples of Data Validation, It shows a list of items as a drop down in a cell, and the user can make a selection from the drop down. This could be In the data validation dialog box, within the settings tab, select List. Extract Data based on a drop-down list selection. Groc.. but in dependent column i am not getting values …what i am doing wrong ? Restrict value entry in cell based on another cell value in Excel. You can achieve it with the Data Validation feature. Please do as follows. 1. Select range B1:E10, then click Data > Data Validation. See screenshot: 2.

This is the solution for my task. The code lets us change the data validation in one cell depending on the datavalidated input in the previous one.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("L21")) Is Nothing Then
    Worksheets(3).Range("L23").Clear
    Select Case Range("L21")
        Case "x": x_projection
        Case "y": y_projection
        Case "z": z_projection
    End Select

End If
End Sub

Sub x_projection()

    Worksheets(3).Range("L23").Validation.Delete
    Worksheets(3).Range("L23").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="y, z"
    Worksheets(3).Range("L23") = "z"

End Sub

Sub y_projection()

    Worksheets(3).Range("L23").Validation.Delete
    Worksheets(3).Range("L23").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="x, z"
    Worksheets(3).Range("L23") = "x"

End Sub

Sub z_projection()

    Worksheets(3).Range("L23").Validation.Delete
    Worksheets(3).Range("L23").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="x, y"
    Worksheets(3).Range("L23") = "y"

End Sub

How to restrict value entry/input in a cell based on another cell value , If you want to learn how to use and apply conditional data validation in Excel, in an Excel drop-down list depending on the value in another cell (or in another will find out how to create conditional Excel drop down using Data Validation. Copy data validation from one cell to another To copy validation from one cell to other cells. Copy the cell (s) normally that contain the data validation you want, then use Paste Special + Validation. Once the dialog appears, type "n" to select validation, or click validation with the mouse.

Excel data validation list based on another data , If you want to validate cells with your own criteria, create a custom times based on the current time), it can also return another numeric value. Select one or more cells to validate, go to the Data tab > Data Tools group, and click the Data Validation button. You can also open the Data Validation dialog box by pressing Alt > D > L, with each key pressed separately. 2. Create an Excel validation rule

Create Dependent Drop-down Lists with Conditional Data Validation , Cell references - make a rule based on a value or formula in another cell. Formulas - allow to express more complex conditions like in this  Set up a named range dd_primary that refers to tbl_primary. Set up the primary drop-down input cell with data validation and allow a list equal to dd_primary. Set up a table to store the secondary drop-down choices named tbl_secondary. Set up a named range dd_secondary that retrieves the related choices.

Creating a Dependent Drop Down List in Excel [Step-by-Step Tutorial], Here is how you can create a drop-down list: Select the cell where you want to show the drop down list. In this example, it is cells C1. Go to Data –> Data Tools –> Data Validation.

Comments
  • Even with a formula, you can use Data Validation on the cell to produce the dropdown. However, if you do this, when even if the cell is N/A, you still have the dropdown available. Also, if they chose any option from the dropdown, the formula is erased. So even if you change back the Yes to a No, N/A won't show again. Are you amenable to VBA for this?
  • VBA would be fine! I wasn't sure if that would complicate the matters too much. So my only question with VBA - is it something that would run on each cell update in A1? Or is it something that would have to be run manually like a macro? Id prefer that it's something that happens automatically..
  • This is easy via VBA. Automatically is good. Let me hack together a sample really quickly. :)
  • Is THIS what you want?
  • @SiddharthRout: Thanks. But, man, that stuff you linked to is just insane. Great job with that!
  • Nah on second thought, I want to use a function instead... j/k this is great :) Thanks much, Ill try it out and get back to you\mark as answer. thx!