drop down list dependence on other cell

Hi bobmc,

Take a look at this posting:

http://www.mrexcel.com/board2/viewtopic.php?t=124404&highlight=

Damon

Also see these posts ...


http://www.vbaexpress.com/forum/showthread.php?t=1072

http://www.mrexcel.com/board2/viewtopic.php?t=40


HTH

These schema work, with the following problem...

A2 has list data validation "=Countries" (which = C2:C4)
B2 has list data validation "=INDIRECT(A2)"

Upon initial selections in A2 and B2, everything works fine.

If I change A2, B2's selection remains the same until I reselect from the options available in the new dropdown.

This potentially results in selections like "France" under "Country" and "Dallas" under "City".

Is there a way, when cell A2 is changed, to have cell B2 automatically change to the top of availables in its respective list?

Here's an example...

I select "USA" in A2. Cell B2 automatically displays "Seattle" (making my named range "USA" available as dropdown.) I then select "Canada" in A2. Cell B2 automatically changes to Quebec with its respective dropdown.

Thanks

bobmc

Hi again bobmc,

I don't believe the method I proposed has the problem you mention.

Damon

Hi Damon...

I must be doing something wrong. Check the sample below. Here are the specs...

E2:E3 range named "Mfr"
F2:F4 range named "MatCatA"
G2:G4 range named "MatCatB"

B4 is validation dropdown, source "=Mfr"
C4 is validation dropdown, source "=MatCat"

"MatCat" is defined name with the following formula
=IF(Sheet1!$B$4="CompanyA",MatCatA,MatCatB)

I've plucked the below sample directly from sheet1 after having initially selected "CompanyA" in dropdown B4, then selecting "Rake" in resulting dropdown C4, then selecting "CompanyB" in dropdown B4. As you can see, the result is a display of CompanyB followed by Rake, which is a CompanyA item. If I click on the dropdown arrow for C4, the appropriate list (MatCatB) is displayed, but the displayed item still remains from my initial selections.

Please let me know if I've screwed this up...wouldn't be the first time!!

Thanks.

bobmc

Damon Ostrander said:

Hi again bobmc,

I don't believe the method I proposed has the problem you mention.

Damon

Click to expand...

Damon,

What bobmc and others want does not bear on the formulas such as INDIRECT(), IF(), CHOOSE(), etc. with which the sublists are activated when the user clicks on the dropdown arrow.

The user selects FRANCE in A2 and Lyons in B2 at time T1. Later, the user selects USA in A2. At the time of this selection, B2 will still show Lyons and will show Lyons until the user also activates B2 in order to make a USA-related choice. It would be a much better feedback to the user if B2 starts blinking and goes blank or shows the top item from the USA-related sublist so that the user is goaded to also make a choice in B2. I think this can better be done with some event code.

Aladin

Aladin and bobmc,

Thank you Aladin for the clarification. Yes, I believe that event code is the only way to do this. Since it is easy to implement dependent lists like this with event code, the only complication is the blinking or clearing of the dependent cells that you mention. I will look into the code for that and post it.

Damon

Okay, here is a VBA/event-based solution. This code example is based on the Country-City example given by bobmc. First, here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = [country].Address Then
With [City].Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlEqual, Formula1:="=" & [country] & "cities"
.IgnoreBlank = True
.InCellDropdown = True
End With
Application.EnableEvents = False
[City].ClearContents
Application.EnableEvents = True
End If
End Sub

To make the code simple I gave the various ranges names:

I named cell A2 "Country"
B2 is "City"
C2:C4 is "Countries"
D2:D4 is "USAcities"
E2:E3 is "FranceCities"
F2:F3 is "CanadaCities"

In the code I could then get the name of the range for each country's cities by taking the value in the Country cell and appending "cities" to it. The code simple clears the City cell whenever the Country cell is changed so that the user must re-select the city.

The only validation list that must be set up manually ahead of time is the one in the Country cell (its list set to the Countries range).

Since this is worksheet event code, the code must be installed in the worksheet's event code module. To do this, right-click on the worksheet's tab, select View Code, and paste the above code into the Code pane.

Damon

Damon Ostrander said:

Okay, here is a VBA/event-based solution.

...

Click to expand...

So, it's not doable without taking over the sublists? Hoped to get something which makes dependent dropdown cells blink and look like white untill attended by the user.