Dynamic Drop Down Based on Another Dropdown

[slam]

Is this possible?

On a worksheet called Setup, a selection is made from a drop down in cell B2 to chose the name of a Racing Class.

On a worksheet called Circuits, multiple types of Racing Class are listed in column A, and the Circuits that make up that Racing Class are listed in column B. One Racing Class can have dozens of circuits, so the Racing Class will be listed multiple times in column A. For instance:

ColumnA ColumnB
Class1 Circuit1
Class1 Circuit2
Class1 Circuit3
Class1 Circuit4
Class2 CircuitA
Class2 CircuitB
Class3 CircuitX
Class3 CircuitY
Class3 CircuitZ
etc

On another worksheet called Season, what I want is to have a drop down menu in B3:B42 that contains a list of Circuits for the chosen Racing Class.

Hopefully this makes sense. Any help would be greatly appreciated.

 

[slam]

I can upload in a bit, but try this first, or explain if you can

In your 2nd workbook, I created a test data validation list unrelated to what we're doing. I made it on the Season worksheet in cell I9. I put random data in I10 and I11, but then I pointed the data validation list to I10:I20, so in other words, I also included several blank cells. Ignore Blanks is checked. When I click the drop down, the blanks are displayed.

I then opened a brand new workbook and repeated the test, and this time no blanks were shown in the drop down list.... so I gather there's something specific to your workbook that is causing this?

 

[slam]

Here's the file where I did that test, and also where I made a macro to copy/paste special/values. It is run manually at the moment, but I guess I would make it part of worksheet calculate or something eventually.

https://dl.dropboxusercontent.com/u...lam-DynamicDV_Keeping_Slams_CircuitSheet.xlsm

 

[mick0005]

I can upload in a bit, but try this first, or explain if you can

In your 2nd workbook, I created a test data validation list unrelated to what we're doing. I made it on the Season worksheet in cell I9. I put random data in I10 and I11, but then I pointed the data validation list to I10:I20, so in other words, I also included several blank cells. Ignore Blanks is checked. When I click the drop down, the blanks are displayed.

I then opened a brand new workbook and repeated the test, and this time no blanks were shown in the drop down list.... so I gather there's something specific to your workbook that is causing this?

The "Ignore blanks" in data validation does not mean it will remove blanks from the list...It basically means that a Blank will be allowed as a valid entry in the cell. That is why we build the offset function into the defined range that the data validation references. The offset allows us to have a dynamicly expanding & contracting. Let me take another look at this though. I am thinking we may be able to get it to work with Sumproduct instead of offset.

 

[slam]

Okay, understood about ignore blanks, but my point remains

Pointing the data validation list to blanks in your workbook results in blanks showing in the drop down list. Pointing the data validation list to blanks in a new workbook does not cause the blanks to show in the drop down list. I'm sure it's something obvious, but not to me

 

[mick0005]

Okay, understood about ignore blanks, but my point remains

Pointing the data validation list to blanks in your workbook results in blanks showing in the drop down list. Pointing the data validation list to blanks in a new workbook does not cause the blanks to show in the drop down list. I'm sure it's something obvious, but not to me

It is odd behavior, but I suspect it is because Data once existed in those cells. If you were to, say, put a value into I20 the list will expand and you'll see the value from I20 with a bunch of blanks in between. Now clear I20 and you'll see that all those blanks still remain in the DV list and it is no longer ignoring blanks. Excel doesn't seem too consistent with this for some reason, but you can be sure it can't be relied on for a solution.

That said, I do have a solution! New formula for defined name "CircuitList" which the DV on Season sheet is now referencing. It does not show blanks and neither does the class selection on the Setup sheet.

Link is here:
https://www.dropbox.com/s/fjz1v2k4758xl5h/DynamicDV_Keeping_Slams_CircuitSheet.xlsx?dl=0

 

[Teun Ham]

Take a look at the "Data Validation - Dependent Lists" examples on www.contextures.com

http://www.contextures.com/tiptech.html

 

[slam]

It is odd behavior, but I suspect it is because Data once existed in those cells. If you were to, say, put a value into I20 the list will expand and you'll see the value from I20 with a bunch of blanks in between. Now clear I20 and you'll see that all those blanks still remain in the DV list and it is no longer ignoring blanks. Excel doesn't seem too consistent with this for some reason, but you can be sure it can't be relied on for a solution.

That said, I do have a solution! New formula for defined name "CircuitList" which the DV on Season sheet is now referencing. It does not show blanks and neither does the class selection on the Setup sheet.

Link is here:
https://www.dropbox.com/s/fjz1v2k4758xl5h/DynamicDV_Keeping_Slams_CircuitSheet.xlsx?dl=0

Oh, this is pretty much the greatest day ever, and not just because this works, but because I changed the layout of my workbook a little, copied all these formulas and named ranges, tweaked them, and it still works! Thank so much!

 

[mick0005]

Oh, this is pretty much the greatest day ever, and not just because this works, but because I changed the layout of my workbook a little, copied all these formulas and named ranges, tweaked them, and it still works! Thank so much!

Wonderful! Glad it worked and I hope you learned something new through all of this. I'm passing this on from someone else who taught me this cascading data validation method awhile back.