two way dependent dropdowns.. possible?

[Nefermet]

Hi there,

I'm a newbie when it comes to VBA or Macros and not sure what function I'd even need to look at to do what I need. So I'm hoping someone can help me on here :)

I'm trying to create a simple user form, where the user can either choose a system or a motor from two separate dropdowns, BUT both of these dropdowns are dependent on the other.

For example... On sheet1 - System dropdown is in B9 and Motor is in C9
If you select a system from B9, a list of applicable motors appears in the dropdown in C9
Or if you select a motor from C9 first, then a list of applicable systems appears in dropdown B9

I've attached my form as it is at the moment, where the dropdowns work only one way... C9 is dependent on the selection in B9.

I'm sure I'll need to use scripting but have no idea where to start ...

[p45cal]

See DVs in cells B9:C10
In the Distinct sheet, column B I've used TRANSPOSE which makes it easier to copy down the data validation cells on Sheet1 and the formulae on the Distinct sheet; you don't have to do this.
When you've got values in a given row for both columns B & C the validation lists will be mutually compatible; the only way to see a full list again is to delete the other value in the same row.

It is a shame we can't easily use table structured references directly in DV.

[Nefermet]

Ah that's amazing thank you, I didn't think to do it like that, much easier than how I was going about it :yes


See DVs in cells B9:C10
In the Distinct sheet, column B I've used TRANSPOSE which makes it easier to copy down the data validation cells on Sheet1 and the formulae on the Distinct sheet; you don't have to do this.
When you've got values in a given row for both columns B & C the validation lists will be mutually compatible; the only way to see a full list again is to delete the other value in the same row.

It is a shame we can't easily use table structured references directly in DV.