# Dynamic Data Validation List in Excel with IF Statement Condition

Get FREE Advanced Excel Exercises with Solutions!

In this article, you will learn about four easy methods for creating a dynamic data validation list in Excel using the IF statement condition. We will be using Microsoft 365 to do this; however, you can use any version of Microsoft Excel and follow this tutorial.

## 4 Handy Approaches to Create a Dynamic Data Validation List in Excel with IF Statement Condition

There will be four methods to create a dynamic data validation list using the IF statement condition. Firstly, we will show you a single IF function to do this. Secondly, we will combine the IF and INDIRECT functions with the name range feature. Thirdly, we will employ a nested IF formula. Lastly, there will be a VBA code to achieve the objective of this article. Moreover, the following animated image shows the dynamic data validation list in action.

### 1. Inserting IF Function to Create a Dynamic Data Validation List

In this section, we will use the IF function to create a dynamic data validation list in Excel. We will create a dropdown list first. Then, we will create a dynamic dropdown list based on the first list.

Steps:

• Firstly, select cell B12.
• Secondly, select “Data Validation…” from the Data tab.

• So, a dialog box will pop up.
• Thirdly, select Allow List.
• Then, type the following formula in the Source field and press OK.

`=\$B\$4:\$C\$4`

• After that, select cell C12 and repeat the previous steps.
• Then, insert the following formula and press OK.

`=IF(\$B\$12=\$B\$4,\$B\$5:\$B\$10,\$C\$5:\$C\$10)`

• Therefore, this will complete the first method.

### 2. Combining IF and INDIRECT Functions

We will combine the IF and INDIRECT functions to create a formula in this section. Before that, we need to use the name range feature. Without further ado, let us go through the procedures.

Steps:

• To begin with, the name ranges as follows:
• B5:B10 as Color_Name
• C5:C10 as Hex_Code
• D5:D10 as RGB_Code
• These names need to be the same as the heading in row 4.

• Then, apply the data validation feature in cell B12. The allowed source is B4:D4.
• After that, use the following formula for cell C12 data validation.

`=IF(\$B\$12="",Color_Name,INDIRECT(\$B\$12))`

Formula Breakdown

• Firstly, when the value of cell B12 is blank, it will return the values from the Color_Name named range.
• Then, when the above condition is false, it will execute the INDIRECT function.
• The INDIRECT function will return the values from the named range.
• Thus, this formula works.
• Finally, we can see the formula works perfectly.

### 3. Using Multiple IF Statements in a Dynamic Data Validation List

In the third method, we will use multiple IF statements in a dynamic data validation list in Excel.

Steps:

• Firstly, apply the data validation feature in cell B12. The allowed source is B4:D4.
• Secondly, use the following formula for cell C12 data validation.

`=IF(\$B\$12=\$B\$4,\$B\$5:\$B\$10,IF(\$B\$12=\$C\$4,\$C\$5:\$C\$10,\$D\$5:\$D\$10))`

Formula Breakdown

• There are two IF functions in this formula. The first one checks if the value from cell B12 is equal to the value from cell B4, then it will return the “Color Name” values.
• IF(\$B\$12=\$C\$4,\$C\$5:\$C\$10,\$D\$5:\$D\$10)
• When the value from cell B12 is equal to the value from cell C4, then it will return the “Hex Code” values.
• Else, it will return the value from the “RGB Code” values.
• Thus, this formula works.
• After doing this, the third method will be completed.

### 4. Applying VBA IF Statement to Create a Dynamic Data Validation List

In this last method, we will use the VBA IF statement to create a dynamic data validation list. We will apply the code inside the sheet, so there will be no need to run the code.

Steps:

• To begin with, apply data validation in cell B12. There can be three values on this from the cell range B4:D4.
• After that, right-click on the sheet named “VBA If Statement”.
• Then, select View Code.

• After that, type the following code.
``````Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
On Error GoTo Exitsub
If Intersect(Target, Range("B12")).Value = Range("B4").Value Then
Formula1:="=B5:B10"
ElseIf Intersect(Target, Range("B12")).Value = Range("C4").Value Then
Formula1:="=C5:C10"
ElseIf Intersect(Target, Range("B12")).Value = Range("D4").Value Then
Formula1:="=D5:D10"
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub``````

VBA Code Breakdown

``````Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
On Error GoTo Exitsub``````
• Firstly, this code will be executed when the sheet value changes. Then, we are enabling the events. Moreover, in case of an error, the code will go to the Exitsub portion.
``````If Intersect(Target, Range("B12")).Value = Range("B4").Value Then
Formula1:="=B5:B10"
ElseIf Intersect(Target, Range("B12")).Value = Range("C4").Value Then
Formula1:="=C5:C10"
ElseIf Intersect(Target, Range("B12")).Value = Range("D4").Value Then
Formula1:="=D5:D10"
End If``````
• Secondly, we are applying the data validation criteria to the cell C12. There are three criteria for the three columns.
``````Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub``````
• Lastly, we are exiting this sub-procedure.
• Lastly, the code will run automatically. If we change any values the data validation list will change accordingly.

## Related Articles

Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF