In dependent data validations the second column should show blank when first one is selected differently

[realdemigod]

Hi,
I have two columns one with a product category (A) and the next one is sub-category (B). I created a dependent validation between these two columns.

A - Laboratory, Chemicals
Laboratory - Microscope, Other equipment
Chemicals - chem1, chem2, chem3

I selected Laboratory under A and then selected Microscope in the column B. But when I change to Chemicals the Microscope still shows under column B. Instead I want to make it blank in column B when I change the validation in column A and be able to select chem1 or chem2 or chem3. Is it possible?

Thanks

 

[Akuini]

Try this:
Put the code in the code module of the sheet where the data validation is located.
Change 'Range("A2:A10")' to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
        If Target.Cells.Count = 1 Then
        Application.EnableEvents = False
             Target.Offset(, 1) = ""
        Application.EnableEvents = True
        End If
    End If
End Sub

 

[realdemigod]

Thank you, let me try and get back to you.

 

[realdemigod]

Thanks it is working, appreciate your help!

edit: I'm unable to change the title so that I can put SOLVED.

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.
We don't have rule to mark a thread as Solved here.

 

[realdemigod]

You're welcome, glad to help, & thanks for the feedback.
We don't have rule to mark a thread as Solved here.

Oh good, you are welcome and thanks for letting me know.

 

[realdemigod]

Hi Akuni, is it possible to do this without a macro?

 

[Akuini]

I don't know how to do this without a macro.
Is it possible?
I'm not sure.

 

[realdemigod]

I don't know how to do this without a macro.
Is it possible?
I'm not sure.

I think so too but wanted to confirm from an expert, thanks.