Drop Down list and functions

[bensa210]

I have 2 drop down list on one excel sheet columns A and B. I would like to know if it is possible to either lock or disable the second list if the other one is chosen already. We cannot have 2 entries on the same row. 

[#2 · Accepted solution]

@bensa210 

In Excel 365, you can use data validation and a bit of VBA (Visual Basic for Applications) code to create a dynamic dropdown list in Column B that depends on the selection in Column A and disable it when a selection is made in Column B. Here is how you can achieve this:

Step 1: Set Up Data Validation

1. In Column A, create your first dropdown list using Excel's data validation feature. You can do this by selecting the cells where you want the dropdowns, going to the "Data" tab, and clicking on "Data Validation." Set the validation criteria based on your first list of options.
2. In Column B, set up another data validation dropdown list. However, this list should be dynamic and dependent on the selection made in Column A. You can do this using Excel's "List" option in data validation and using a formula to generate the list based on the selection in Column A.

Step 2: Create a Dynamic Dropdown List

1. Select the cells in Column B where you want the second dropdown list.
2. Go to the "Data" tab, click on "Data Validation," and choose "List" as the Allow option.
3. In the "Source" field, enter a formula that generates the list based on the selection in Column A. For example, if your options for Column A are in cells A2:A10 and you want the options in Column B to be in cells B2:B10, you can use a formula like this:

=IF(A2<>"", B2:B10, "")

This formula will display the options in B2:B10 only if a selection is made in the corresponding cell in Column A.

Step 3: Disable the Dropdown List

To disable the dropdown list in Column B when a selection is made in Column A, you can use VBA. Here is an example of VBA code that disables the dropdown list in Column B when a selection is made in Column A:

vba code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then ' Check if the change occurred in Column A
        If Target.Value <> "" Then
            ' Disable the dropdown list in Column B
            Me.Range("B2:B" & Me.Cells(Me.Rows.Count, "B").End(xlUp).Row).Validation.Delete
        End If
    End If
End Sub

To use this code:

1. Press Alt + F11 to open the VBA editor in Excel.
2. In the VBA editor, locate your worksheet under "Microsoft Excel Objects" in the Project Explorer.
3. Double-click on the worksheet to open its code window.

1. Paste the code provided above into the code window.

Now, when a selection is made in Column A, the dropdown list in Column B will be disabled, and the user will not be able to make another selection in the same row. The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.

[#3]

@NikolinoDE

Good morning, I want to thank you for the information. It did work and I was finally able to accomplish my task for work. Many thanks sir

Ben Charles