Extension of Dependent Pulldown Lists

[gototcm]

This should be a simple extension of Dynamic pulldown Lists, but i
isn’t obvious.

I have a simple pulldown list in cell a1, simply a,b,c done through dat
validation. In cell a3 I created a dependent list so that if I selecte
“a” in cell a1, I have a pulldown list that read apples, acorns. If
selected “b” the depedent list is bubbles, baskets. I know how to d
this. I created a name “a” with two entries apples and acorns and did
similar thing for the name “b.” This all works as advertised.

Here’s the rub. Suppose I select “a” in cell a1, and then apples i
cell a3. But if go to cell a1 and clear the contents, the null string
I want cell a3 to also clear. It doesn’t and I can’t figure out how t
accomplish this.

The end application is more complex and has several of these situation
so that is why I am asking

 

[Don Guillett]

Maybe this will be helpful. I have a macro tied to dv cells so you
could have a worksheet_change event tied to a1 that clears or puts a
value or "" in a3

'=======Outgo
If Target.Address = Range("outgo").Address Then
Application.ScreenUpdating = False
With Sheets("Setup")
mr = .Range("outgorange").Find(Target).Row
If Len(Trim(Cells(myrow, "d"))) < 1 Then
Cells(myrow, "b").Resize(, 6).Value = _
..Cells(mr, "c").Resize(, 6).Value
Cells(myrow, "d").Select
Else
Cells(myrow, "f").Select
End If
End With
Application.EnableEvents = False
Target = "Outgo"
Range("Outgo").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End If

 

[GS]

gototcm formulated the question :

This should be a simple extension of Dynamic pulldown Lists, but it
isn’t obvious.

I have a simple pulldown list in cell a1, simply a,b,c done through data
validation. In cell a3 I created a dependent list so that if I selected
“a†in cell a1, I have a pulldown list that read apples, acorns. If I
selected “b†the depedent list is bubbles, baskets. I know how to do
this. I created a name “a†with two entries apples and acorns and did a
similar thing for the name “b.†This all works as advertised.

Here’s the rub. Suppose I select “a†in cell a1, and then apples in
cell a3. But if go to cell a1 and clear the contents, the null string,
I want cell a3 to also clear. It doesn’t and I can’t figure out how to
accomplish this.

The end application is more complex and has several of these situations
so that is why I am asking.

I do a similar thing in a simple bookkeeping app where the 'Income' and
'Expense' ledgers have account dropdowns and dependant sub-account
dropdowns. Users must select an account, sub-account, and enter an
amount for each dated transaction. What displays in the sub-account
dropdown depends what was selected in the account dropdown, making the
sub-account dropdown 'context-sensitive' to the account dropdown. If a
user changes a selection in the account dropdown, the sub-account
dropdown turns red (both pattern and font) because its contents are no
longer 'in context' with its respective account dropdown. The behavior
of the sub-account is managed by CF based on the value in its
respective account dropdown.

The only way you can reset/clear the dependant cell is via VBA in a
_Change event. Otherwise, you could implement CF to 'cue' users that
something is missing in A1 and/or the contents of A3 are 'out of
context'. As you state in your final paragraph, this leads to the
project being somewhat complex. How you design the project is the key
to how easy the complexity is to manage!<g>