Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Unhappy Extension of Dependent Pulldown Lists

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,522
Default Extension of Dependent Pulldown Lists

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



On Jan 23, 8:39*pm, gototcm wrote:
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.

--
gototcm


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,514
Default Extension of Dependent Pulldown Lists

gototcm formulated the question :
This should be a simple extension of Dynamic pulldown Lists, but it
isnt 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.

Heres 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 doesnt and I cant 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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dependent lists in other workbooks Julien Excel Worksheet Functions 2 June 4th 08 12:34 PM
dependent lists - error?? Steve_n_KC Excel Discussion (Misc queries) 0 June 28th 07 09:24 PM
Autofill/Autocomplete Prediction PullDown Lists chris100 Excel Discussion (Misc queries) 3 August 17th 06 09:27 PM
Dependent Lists in Excel rajeshkumar Excel Worksheet Functions 14 July 13th 06 08:15 PM
Dependent lists AppraiserRon Excel Worksheet Functions 1 June 2nd 05 03:36 PM


All times are GMT +1. The time now is 10:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"