Hi,

Let's say you have 2 validation lists, the first one being the "parent" cell
and then the second one being the "child" cell. The list in the childs cell
is based on the selection made in the parents cell. Now my question is if i
select i new value in the parent cell how can i get the child cell to clear?

I tried this but it didn't work,

=IF(G8="","",IF(G8="All",Div,OFFSET(Dept,MATCH(G8, DeptCol,0)-1,1,COUNTIF(DeptCol,G8),1)))

If you want to actually clear the second cell, you're going to have to use a
macro--maybe an event macro like this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Set myCell = Me.Range("a1")
If Intersect(Target, myCell) Is Nothing Then Exit Sub

If IsEmpty(myCell.Value) Then
Application.EnableEvents = False
me.range("B1").Value = ""
Application.EnableEvents = True
End If

End Sub

If you want to try, I had my parent cell as A1 and my child as B1.

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this in (adjust those addresses). Then back to excel.


Ben wrote:

Hi,

Let's say you have 2 validation lists, the first one being the "parent" cell
and then the second one being the "child" cell. The list in the childs cell
is based on the selection made in the parents cell. Now my question is if i
select i new value in the parent cell how can i get the child cell to clear?

I tried this but it didn't work,

=IF(G8="","",IF(G8="All",Div,OFFSET(Dept,MATCH(G8, DeptCol,0)-1,1,COUNTIF(DeptCol,G8),1)))

--

Dave Peterson