Create a data validation in a cell based on the value in another cell
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):
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
Yes is entered in Column A (note the dropdown arrow):
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
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:
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.
- 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/Awon'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!