drop down list dependence on other cell


Board Regular
Mar 13, 2002

Cells B1 and B2 are validation drop down lists derived from ranges D2:D4 and E2:E4 respectively.

Let's say the only shape available in the color "Green" is "Triangle". If "Green" is selected, I'd like cell B2's dropdown to either default to "Triangle" without any other option available, or have cell B2 display a message to the effect, "Green is only available in Triangle.".

Any ideas?



Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.


Upvote 0
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

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!!


Upvote 0
Damon Ostrander said:
Hi again bobmc,

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



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.

Upvote 0
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.

Upvote 0
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
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlEqual, Formula1:="=" & [country] & "cities"
.IgnoreBlank = True
.InCellDropdown = True
End With
Application.EnableEvents = False
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.

Upvote 0
Damon Ostrander said:
Okay, here is a VBA/event-based solution.


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.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back