How to create a multiple drop-down lists in Excel using Developer?

[PetarTen]

Hi Folks, I'm struggling to find a short Video where to see how I can make a Multiple Drop-down Lists using Developer Mode Box.
I don't want to use the data validation tool for several reasons, so if anyone here has come across a video or online lecture or presentation where this "trick" is shown - please comment


Btw, this article here is useless - Insert a multiple-selection list box - Microsoft Support
The idea is to make a Multiple Dropdown Lists like this guy here:

...but using the Developer.
Cheers!

 

[PetarTen]

Sorry, there is no named formula. The formula that fills the ENGINE combobox is in cell F3. But it only returns a single value. If you want multiple values the whole thing needs to be redesigned.

If I am going to be able to help I need a very specific set of requirements from you. I have already spent a lot of time and we still do not have usable results.

When you select BP in the OIL combobox, what items do you want to be available in the ENGINE combobox? What do you want the default selection to be?
When you select Shell in the OIL combobox, what items do you want to be available in the ENGINE combobox? What do you want the default selection to be?

Hi Jeff,
A week later...
Is there some progress on the task?

 

[6StringJazzer]

I have been on vacation and am not able to address this right now. This thread is available to all users so hopefully someone else is willing to help further.

 

[PetarTen]

I have been on vacation and am not able to address this right now. This thread is available to all users so hopefully someone else is willing to help further.

Sometimes, Life sucks!
Can't even open your Version of the Table

 

[PetarTen]

Sorry, there is no named formula. The formula that fills the ENGINE combobox is in cell F3. But it only returns a single value. If you want multiple values the whole thing needs to be redesigned.

If I am going to be able to help I need a very specific set of requirements from you. I have already spent a lot of time and we still do not have usable results.

When you select BP in the OIL combobox, what items do you want to be available in the ENGINE combobox? What do you want the default selection to be?
When you select Shell in the OIL combobox, what items do you want to be available in the ENGINE combobox? What do you want the default selection to be?

Hi Jeff,
Here's the follow-up Thread:
Multiple values
I'll keep my fingers crossed that you'll cast an eye

 

[PetarTen]

I have been on vacation and am not able to address this right now. This thread is available to all users so hopefully someone else is willing to help further.

Okay, I'm ready!
I did it!
The Table now has two Multiple Dropdown lists BUT...only 1 thing I couldn't solve - when Shell is chosen on the left (A2), the Dropdown list on the right (B2) starts from 6 Cylinder and not 4 Cylinder.
Can you help me with this Jeff?

Here's the file:

Filebin | t6z72gpf15eev2sb

 

[6StringJazzer]

You have formulas in F3:F6 that select the list for the ENGINE dropdown based on what was chosen in the OIL dropdown. If BP is chosen, BP is the first oil so it starts with the first enginer (4 cylinder). If Shell is chosen, Shell is the second oil so it starts with the second engine (6 cylinder). That is exactly how yo have coded it.

What do you want to happen instead? How is the list for the ENGINE dropdown dependent on what was chosen for OIL?

 

[PetarTen]

You have formulas in F3:F6 that select the list for the ENGINE dropdown based on what was chosen in the OIL dropdown. If BP is chosen, BP is the first oil so it starts with the first enginer (4 cylinder). If Shell is chosen, Shell is the second oil so it starts with the second engine (6 cylinder). That is exactly how yo have coded it.

What do you want to happen instead? How is the list for the ENGINE dropdown dependent on what was chosen for OIL?

I simply want to avoid this First, Second, Third etc… deduction and want no matter what Oil I choose in A2, the Engine dropdown list always to start with 4 Cylinder.

Cannot answer your second question

 

[PetarTen]

You have formulas in F3:F6 that select the list for the ENGINE dropdown based on what was chosen in the OIL dropdown. If BP is chosen, BP is the first oil so it starts with the first enginer (4 cylinder). If Shell is chosen, Shell is the second oil so it starts with the second engine (6 cylinder). That is exactly how yo have coded it.

What do you want to happen instead? How is the list for the ENGINE dropdown dependent on what was chosen for OIL?

PLEASE DISREGARD THE UPPER ANSWER!!!

Here’s the correct answer:

I simply want to avoid this First, Second, Third etc… deduction (row-change) and want to have different Engine types (Number of cylinders) with accordance to the Oil I choose.
For instance when I choose Shell - to have only 4, 8 and 10 cylinder Engines in the B2 dropdown List.
There must be some sort of relation between the Oils in A2 Cell, Sheet1 and the Table4 on Sheet2…or it must be specially written in the Formulas, Column F or E, on Sheet1 (which is better I think).

Cannot answer your second question

 

[6StringJazzer]

[...]when I choose Shell - to have only 4, 8 and 10 cylinder Engines in the B2 dropdown List.
There must be some sort of relation between the Oils in A2 Cell, Sheet1 and the Table4 on Sheet2…or it must be specially written in the Formulas, Column F or E, on Sheet1 (which is better I think).

Cannot answer your second question

Your answer is what I'm asking in my second question.

You have to be able to tell me exactly the relation between the oil selected in A2, and the engines available in the second dropdown. I cannot write a formula for "some sort of relation."

 

[PetarTen]

Your answer is what I'm asking in my second question.

You have to be able to tell me exactly the relation between the oil selected in A2, and the engines available in the second dropdown. I cannot write a formula for "some sort of relation."

I think there must be an additional Table on Sheet2 where should be written the relation between the Oils and Engines. Right?
And it is a simple relation - when choose BP, you should have 4, 6, 8, 10 and 12 cylinder Engines (and they must appear in the B2 dropdown list)
When choose Shell, you should have 4, 8 and 10 cylinder Engines ONLY (in the same B2 dropdown list)

Underneath Shell, you should be able to add many more Oils and they must correspond to different Cylinder type Engines...as you desire (Aspen - 4, 6 and 8 cylinder Engines ONLY, Castrol - 8, 10 and 12 cylinders ONLY, Mobil1 - 6, 10 and 12 cylinders ONLY) etc...
In other words - if those Engines are written in a Table, the primary 4, 6, 8, 10 and 12 Cylinders types (sitting in exact Cells), should be able to get enumerated within the Formula as per desire.
Somewhere here maybe?
=IFERROR(INDEX(Sheet2!$A$7:A$11,MATCH($A$2,Sheet2!$A$3:$A$4,0)),"")

But first - there must be a third Table created on Sheet2, or Sheet1.
Or not?