A better way to create dependent flexible drop down lists?

[Fatabuna]

I have a spreadsheet with four dependent drop-down lists in the following cells: </SPAN>

Region – Summary!$C$4</SPAN>
Director – Summary!$C$6</SPAN>
Client – Summary!$C$8</SPAN>
Category – Summary!$C$10</SPAN>

The lists are dependent in this order, i.e. when you select a certain region, Director list is limited to names in that specific region. When you then also select a director, you will only see clients under that Region–Director combination.</SPAN>

The lists are fed from another sheet called “Lists”:</SPAN>

[TABLE="width: 740"]
<TBODY>[TR]
[TD][/TD]
[TD]A</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]D</SPAN>
[/TD]
[TD]E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]G</SPAN>
[/TD]
[TD]H</SPAN>
[/TD]
[TD]I</SPAN>
[/TD]
[TD]J</SPAN>
[/TD]
[TD]K</SPAN>
[/TD]
[TD]L</SPAN>
[/TD]
[TD]M</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD]Region</SPAN>
[/TD]
[TD][/TD]
[TD]Region</SPAN>
[/TD]
[TD]Director</SPAN>
[/TD]
[TD][/TD]
[TD]Region</SPAN>
[/TD]
[TD]Director</SPAN>
[/TD]
[TD]Client</SPAN>
[/TD]
[TD][/TD]
[TD]Region</SPAN>
[/TD]
[TD]Director</SPAN>
[/TD]
[TD]Client</SPAN>
[/TD]
[TD]Category</SPAN>
[/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD]EAST</SPAN>
[/TD]
[TD][/TD]
[TD]EAST</SPAN>
[/TD]
[TD]JOHN</SPAN>
[/TD]
[TD][/TD]
[TD]EAST</SPAN>
[/TD]
[TD]JOHN</SPAN>
[/TD]
[TD]HONDA</SPAN>
[/TD]
[TD][/TD]
[TD]EAST</SPAN>
[/TD]
[TD]JOHN</SPAN>
[/TD]
[TD]HONDA</SPAN>
[/TD]
[TD]HATCHBACK</SPAN>
[/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD]WEST</SPAN>
[/TD]
[TD][/TD]
[TD]WEST</SPAN>
[/TD]
[TD]MARK</SPAN>
[/TD]
[TD][/TD]
[TD]EAST</SPAN>
[/TD]
[TD]JOHN</SPAN>
[/TD]
[TD]TOYOTA</SPAN>
[/TD]
[TD][/TD]
[TD]EAST</SPAN>
[/TD]
[TD]JOHN</SPAN>
[/TD]
[TD]HONDA</SPAN>
[/TD]
[TD]SEDAN</SPAN>
[/TD]
[/TR]
[TR]
[TD]4</SPAN>
[/TD]
[TD]CENTRAL</SPAN>
[/TD]
[TD][/TD]
[TD]WEST</SPAN>
[/TD]
[TD]PETER</SPAN>
[/TD]
[TD][/TD]
[TD]WEST</SPAN>
[/TD]
[TD]MARK</SPAN>
[/TD]
[TD]BMW</SPAN>
[/TD]
[TD][/TD]
[TD]EAST</SPAN>
[/TD]
[TD]JOHN</SPAN>
[/TD]
[TD]HONDA</SPAN>
[/TD]
[TD]SUV</SPAN>
[/TD]
[/TR]
[TR]
[TD]5</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]CENTRAL</SPAN>
[/TD]
[TD]IAN</SPAN>
[/TD]
[TD][/TD]
[TD]WEST</SPAN>
[/TD]
[TD]MARK</SPAN>
[/TD]
[TD]FORD</SPAN>
[/TD]
[TD][/TD]
[TD]EAST</SPAN>
[/TD]
[TD]JOHN</SPAN>
[/TD]
[TD]TOYOTA</SPAN>
[/TD]
[TD]HATCHBACK</SPAN>
[/TD]
[/TR]
[TR]
[TD]6</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]CENTRAL</SPAN>
[/TD]
[TD]BILL</SPAN>
[/TD]
[TD][/TD]
[TD]WEST</SPAN>
[/TD]
[TD]PETER</SPAN>
[/TD]
[TD]MERCEDES</SPAN>
[/TD]
[TD][/TD]
[TD]EAST</SPAN>
[/TD]
[TD]JOHN</SPAN>
[/TD]
[TD]TOYOTA</SPAN>
[/TD]
[TD]SEDAN</SPAN>
[/TD]
[/TR]
[TR]
[TD]7</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WEST</SPAN>
[/TD]
[TD]PETER</SPAN>
[/TD]
[TD]AUDI</SPAN>
[/TD]
[TD][/TD]
[TD]WEST</SPAN>
[/TD]
[TD]MARK</SPAN>
[/TD]
[TD]BMW</SPAN>
[/TD]
[TD]SEDAN</SPAN>
[/TD]
[/TR]
[TR]
[TD]8</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CENTRAL</SPAN>
[/TD]
[TD]IAN</SPAN>
[/TD]
[TD]CITROEN</SPAN>
[/TD]
[TD][/TD]
[TD]WEST</SPAN>
[/TD]
[TD]MARK</SPAN>
[/TD]
[TD]BMW</SPAN>
[/TD]
[TD]ESTATE</SPAN>
[/TD]
[/TR]
[TR]
[TD]9</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CENTRAL</SPAN>
[/TD]
[TD]IAN</SPAN>
[/TD]
[TD]PEUGEOT</SPAN>
[/TD]
[TD][/TD]
[TD]WEST</SPAN>
[/TD]
[TD]MARK</SPAN>
[/TD]
[TD]FORD</SPAN>
[/TD]
[TD]HATCHBACK</SPAN>
[/TD]
[/TR]
[TR]
[TD]10</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CENTRAL</SPAN>
[/TD]
[TD]BILL</SPAN>
[/TD]
[TD]FIAT</SPAN>
[/TD]
[TD][/TD]
[TD]WEST</SPAN>
[/TD]
[TD]MARK</SPAN>
[/TD]
[TD]FORD</SPAN>
[/TD]
[TD]SEDAN</SPAN>
[/TD]
[/TR]
[TR]
[TD]11</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WEST</SPAN>
[/TD]
[TD]MARK</SPAN>
[/TD]
[TD]FORD</SPAN>
[/TD]
[TD]ESTATE</SPAN>
[/TD]
[/TR]
[TR]
[TD]12</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WEST</SPAN>
[/TD]
[TD]PETER</SPAN>
[/TD]
[TD]MERCEDES</SPAN>
[/TD]
[TD]SEDAN</SPAN>
[/TD]
[/TR]
[TR]
[TD]13</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WEST</SPAN>
[/TD]
[TD]PETER</SPAN>
[/TD]
[TD]MERCEDES</SPAN>
[/TD]
[TD]ESTATE</SPAN>
[/TD]
[/TR]
[TR]
[TD]14</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WEST</SPAN>
[/TD]
[TD]PETER</SPAN>
[/TD]
[TD]MERCEDES</SPAN>
[/TD]
[TD]CONVERTIBLE</SPAN>
[/TD]
[/TR]
[TR]
[TD]15</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WEST</SPAN>
[/TD]
[TD]PETER</SPAN>
[/TD]
[TD]AUDI</SPAN>
[/TD]
[TD]ESTATE</SPAN>
[/TD]
[/TR]
[TR]
[TD]16</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WEST</SPAN>
[/TD]
[TD]PETER</SPAN>
[/TD]
[TD]AUDI</SPAN>
[/TD]
[TD]SPORTS</SPAN>
[/TD]
[/TR]
[TR]
[TD]17</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CENTRAL</SPAN>
[/TD]
[TD]IAN</SPAN>
[/TD]
[TD]CITROEN</SPAN>
[/TD]
[TD]SEDAN</SPAN>
[/TD]
[/TR]
[TR]
[TD]18</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CENTRAL</SPAN>
[/TD]
[TD]IAN</SPAN>
[/TD]
[TD]CITROEN</SPAN>
[/TD]
[TD]ESTATE</SPAN>
[/TD]
[/TR]
[TR]
[TD]19</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CENTRAL</SPAN>
[/TD]
[TD]IAN</SPAN>
[/TD]
[TD]PEUGEOT</SPAN>
[/TD]
[TD]HATCHBACK</SPAN>
[/TD]
[/TR]
[TR]
[TD]20</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CENTRAL</SPAN>
[/TD]
[TD]IAN</SPAN>
[/TD]
[TD]PEUGEOT</SPAN>
[/TD]
[TD]SEDAN</SPAN>
[/TD]
[/TR]
[TR]
[TD]21</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CENTRAL</SPAN>
[/TD]
[TD]IAN</SPAN>
[/TD]
[TD]PEUGEOT</SPAN>
[/TD]
[TD]ESTATE</SPAN>
[/TD]
[/TR]
[TR]
[TD]22</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CENTRAL</SPAN>
[/TD]
[TD]BILL</SPAN>
[/TD]
[TD]FIAT</SPAN>
[/TD]
[TD]HATCHBACK</SPAN>
[/TD]
[/TR]
[TR]
[TD]23</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CENTRAL</SPAN>
[/TD]
[TD]BILL</SPAN>
[/TD]
[TD]FIAT</SPAN>
[/TD]
[TD]SEDAN</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


Since the lists need to be flexible (new clients, director changes, etc.) as well as dependent, I used the following formula to create Director list:</SPAN>

=OFFSET(Lists!$D$1,MATCH(Summary!$C$4,Lists!$C:$C,0)-1,0,COUNTIF(Lists!$C:$C,Summary!$C$4),1)</SPAN>

The Client list formula is a bit longer:</SPAN>

=OFFSET(Lists!$H$1,MATCH(Summary!$C$4,Lists!$F:$F,0)+MATCH(Summary!$C$6,OFFSET(Lists!$G$1,MATCH(Summary!$C$4,Lists!$F:$F,0)-1,0,50000,1),0)-2,0,COUNTIFS(Lists!$F:$F,Summary!$C$4,Lists!$G:$G,Summary!$C$6),1)</SPAN>

The Category list formula is even longer:</SPAN>

=OFFSET(Lists!$M$1,MATCH(Summary!$C$4,Lists!$J:$J,0)+MATCH(Summary!$C$6,OFFSET(Lists!$K$1,MATCH(Summary!$C$4,Lists!$J:$J,0)-1,0,50000,1),0)+MATCH(Summary!$C$8,OFFSET(OFFSET(Lists!$L$1,MATCH(Summary!$C$4,Lists!$J:$J,0)-1,0),MATCH(Summary!$C$6,OFFSET(Lists!$K$1,MATCH(Summary!$C$4,Lists!$J:$J,0)-1,0,50000,1),0)-1,0,50000,1),0)-3,0,COUNTIFS(Lists!$J:$J,Summary!$C$4,Lists!$K:$K,Summary!$C$6,Lists!$L:$L,Summary!$C$8),1)</SPAN>

If I needed to create another level, I doubt the formula would fit. Is there an easier/more elegant way to do this in excel, or would this require VBA?</SPAN>

I am using Windows 7 and Excel 2010.</SPAN>

Many thanks.</SPAN>

 

[snb_]

You might use the code in the attachment.
As you may notice you only need to put the table somewhere. The VBA code produces the validationlists on the fly.

http://www.excelforum.com/attachment.php?attachmentid=260007&d=1377202397

 

[Fatabuna]

Thanks. Would you be able to copy and paste the code here please? The link takes me to a logon screen of a forum I'm not a member of.

 

[snb_]

So just sign in : it won't harm you.

 

[Fatabuna]

No, it won't, but I prefer not to create one-off accounts left and right.

If it's too much trouble to copy and paste then don't worry about it.

 

[dmt32]

Hi,

I don't have much spare time today to assist but have a look here:Sample Excel Spreadsheets - Excel Templates
They have lots of examples of dependant validation which you may be able to adapt to meet your need and best bit is that you can download for free and without the need to create an account to access.

If you find something that almost does what you want but not sure how to change, post code back here with & explanation & someone here will find time to assist you.

Hope helpful

Dave

 

[Fatabuna]

Thanks, Dave, I appreciate your help. I'll go through the chapters to see if anything there could be used with some modification.