Clearing values in a dependent data validation list

Hi,

I have set up a dependent data validation list whereby Column A = Departments and Column B = Staff Names. The values available in Column B are dependent on what is selected in Column A.

If a value in Column A is deleted, is there a way of automatically clearing the corresponding Column B value? I am using approximately 1000 cells in each column.

Thanks for your help!

At first glance I would say the easiest way to do this is with VBA.

Are you saying that you have one set of data in Column A that is a validation list, or that the cells in A have data validation that use a list defined somewhere else?

Same kind of question for column B. How are you managing the change in data validation choices for staff based on the selection of department?

You would need VBA to do this. Right click on your worksheet Tab and "View Code" Then paste in this code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then     'where 1 is your column, in this case A (1)
Target.Offset(0, 1) = ""

End If
End Sub

Does this work for you?

VBA is an option and probably what I would use. But you can do something "almost" as good with conditional formatting. You can use CF to check if the value in column B matches to the named range in column A, and if it is NOT a match any longer, discolor the cell in a harsh way.

Here's a sample sheet showing what I mean:

DependentLists3.xls