Dynamic Drop Down Based on Another Dropdown

slam

Well-known Member
Joined
Sep 16, 2002
Messages
848
Office Version
  1. 365
  2. 2019
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Is this list going to grow or change frequently? The most straight forward way to do this is actually to set up named ranges. However, if the lists are going to change a lot, then this isn't a good way to do it. I have another method that is pretty dynamic if that is the case.
 
Upvote 0
Is this list going to grow or change frequently? The most straight forward way to do this is actually to set up named ranges. However, if the lists are going to change a lot, then this isn't a good way to do it. I have another method that is pretty dynamic if that is the case.

It will change from time to time, yes.
 
Upvote 0
Are you married to that Circuit sheet setup, or could you do it differently... think like this....


Excel 2010
ABCD
1ClassListClass1Class2Class3
2Class1Circuit1CircuitACircuitX
3Class2Circuit2CircuitBCircuitY
4Class3Circuit3CircuitCCircuitZ
5Circuit4CircuitDCircuitAA
6Circuit5CircuitECircuitAB
7Circuit6CircuitAC
8Circuit7
9Circuit8
10Circuit9
11Circuit10
12Circuit11
13Circuit12

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
DV Setup
 
Upvote 0
I was bored so gave it a go in 2 ways.

The first is keeping your format for the circuit sheet. With this, you can continue to input Classes and Circuits in the circuit sheet and they will automatically show up in my DV_Setup sheet in the correct format I need for the Dynamic Data validation. The trade off with this method is that I can't figure out a way for the dropdowns to not contain blanks. The offset formulas (or table setup w/ indirect) that I would normally use to prevent this will not work because I have to drag arrays down in order to bring in your values from the circuit sheet. In this method, the data validation dropdowns will always contain the right selections, but they will also contain blank selections, which is annoying.

My suggestion is to use my 2nd workbook which relies only on my "Class & Circuit Setup" worksheet that you would maintain based on the configuration of your classes and circuits. It is very easy to maintain, and with this method you won't have blanks in your drop downs.

Let me know how you go. I am interested to hear back.

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

https://www.dropbox.com/s/j3ht07teinesfs6/DynamicDV_wOut_Slams_CircuitFormat.xlsx?dl=0
 
Upvote 0
Also, do note that if you change the selection on your setup sheet (say from class1 to class2) it will not clear out your selections in B3:B43 on the Season sheet. You'd need to go manually change the selections from the updated drop downs on the Season sheet.

You could write some VBA to clear all of the cells in Worksheet (Season).Range ("B3:B43") if the value in Worksheet (Setup).Range ("B3") was to change (i.e. =""), but that is a completely different request, so if you need something like that, I suggest you open a new thread.
 
Upvote 0
Thanks for your help.


I'd really prefer to keep the format of my circuit worksheet because there's a lot of additional information in adjacent columns, and the layout/maintenance/sorting is just a lot easier that way.


I see you have the same data validation formula and Array for each method, so are the blanks in the drop down in method 1 just the result of pointing to a cell range containing a formula vs method 2 pointing to a cell range contaning text ("Ignore Blanks")?


If so, is it possible in method 1 to just simulate method 2 by doing an automatic copy/paste special/values of the DV Setup worksheet, and then have the data validation point to that? maybe the copy/paste could be done as a worksheet activate for the Season worksheet?
 
Upvote 0
Yes, the expanding range in the data validation relies on using offset combined with a count of the non-empty cells to determine the height of the range. Since the method I used to transpose your season sheet into the format I need for the dynamic DV relies on some array formulas on the DV Setup worksheet, technically those cells are no longer "non-empty" cells.

I suppose that there would be a way to turn these into values when opening the file and then rebuild the formulas using VBA before save or something like that. Better yet, maybe someone can write code that would simply turn your format on Circuit into the format we need it on the DV Setup worksheet so we avoid the formulas altogether. However, I am not you guy for the code... I am a real VBA novice at the moment and have only learned what I have so far through necessity and with the help of folks on here.

Maybe start a new thread to see if someone can turn your Circuit columns into my DV Setup columns.

By the way, I made a slight improvement to Row 1 of the DV Setup. I was using a stupid formula before to do it. Now I am using transpose, which is much better. I saved the file back to dropbox, so you should be able to DL an updated one.

Let me know how you go. Would be interested in seeing the completed VBA if someone can write it.
 
Upvote 0
Annoyingly, I wrote a long reply to this last night and it doesn't seem to have saved....

In short, I created some VBA to do a copy/paste special/values, and your drop downs are now pointing to those values instead of the formulas.... but for some reason, the drop downs still include blanks? I must be missing something....
 
Upvote 0
Annoyingly, I wrote a long reply to this last night and it doesn't seem to have saved....

In short, I created some VBA to do a copy/paste special/values, and your drop downs are now pointing to those values instead of the formulas.... but for some reason, the drop downs still include blanks? I must be missing something....

Upload the file again and I will take a look at it. The only thing I am thinking is that the array formula I wrote, it will output "" when it stops finding text to output. When you paste values over it, maybe COUNTA is seeing the "" as non-blank still.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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
Back
Top