Data Validation Lists

  • Thread starter Thread starter Joseph
  • Start date Start date
J

Joseph

Hi,

I have two cells with data validation lists. The values in
the second list are populated based on the value selected
in the first list. For eg:

Let A1 = Vegetable, Fruit
If A1 = Vegetable then
B1 = Carrot, Potato

If A1 = Fruit then
B1 = Apple, Grape

Ive made the list to work, with the Indirect () function,
but have one small problem. The first time I select, A1 =
Vegetable and B1 = Carrot, then, if I change A1
to "Fruit", is it possible to clear the contents of the
cell B1 as "Carrot" is not a valid value for "Fruit". Is
this possible without writing a macro ??

Thanks
Joseph
 
Joseph,
Is this possible without writing a macro ??
Click to expand...

No. You would need to use the Worksheet's change event to capture the
change in value in Cell A1 to clear cell B1.

Copy the code below, right click on the sheet tab, select "View Code"
then paste the code into the window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("B1").ClearContents
End If
End Sub
 
Back
Top