Creating multiple (5) drop down lists, all dependent on the selection(s) of the prior

[Andrea.Taylor]

I need to put together a worksheet that assists users in selecting a valid account for posting activity. Prior to viewing a list of acceptable account numbers (with full descriptions), the user must 1st select from 4 other drop downs.

Column A has 2 possible selections
Column B has 2 possible selections
Column C has 8 possible selections
Column D has 20 possible selections
Column E has 36 possible selections

Column A and B are not dependant on each other
Column C is dependant on the combined selections in Col A and B
Column D is dependant on the combined selections in Col A, B and C
Column E is dependant on the combined selections made in Col A, B, C and D

I've read many posts that reference cascading drop down lists. I have not been able to find a post that 1) addresses having more than 2 dependant lists, nor 2) avoiding the need to create dependent lists (using the Name and Indirect function) for each possible combination

The Data I'm building off of is not static though major changes are not expected to occur often.

I'm pretty lost here. I'd greatly appreciate any help.

Thanks

 

[VoG]

This deals with 3 dependent lists http://www.contextures.com/xlDataVal02.html

I think that this is going to be fiddly to set up.

 

[Andrea.Taylor]

Hi Thanks Vog, I actually already reviewed that link among many others this past weeked. The issue I have with this approach is that I would need to make multiple dependent list for each potential combination. Using a pivot table off my data, this would equate to nearly 100 named lists (exaggerating a bit).

I'm hoping there's a better approach.

 

[chy105]

Did you find a solution without having to create all the different combinations? I have the same problem.

 

[will31]

You can adapt this approach to suit your needs.

If you name each of the range as the predessor then it works fine e.g.;

Turkey
Chicken
Pork

Name the dependants to Turkey as Turkey then name the dependants of the dependants as the dependant.

Put =INDIRECT(cell reference for antecedent e.g. c2) into the data validation.

 

[Andrea.Taylor]

Did you find a solution without having to create all the different combinations? I have the same problem.

No, not yet. I've been searching all over the web in still no answer. If you find something, please let me know

 

[Andrea.Taylor]

You can adapt this approach to suit your needs.

If you name each of the range as the predessor then it works fine e.g.;

Turkey
Chicken
Pork

Name the dependants to Turkey as Turkey then name the dependants of the dependants as the dependant.

Put =INDIRECT(cell reference for antecedent e.g. c2) into the data validation.

Will31 - it seems like this method is only functional when you have limited lists. Is there a method that can be applied with lists that may contain more than just a few items? I would like to avoide creating a great amount of named lists especially since the data can change over time.

 

[bkissick]

Just fill dropdowns C,D,E as A and B are filled dynamically? Would seem to be pretty straightforward to do programatically, just attach to a .selected or .changed trigger in each dropdown. What am I missing?

 

[Epst13n]

Andrea, I was wondering (translated: eagerly hoping) that you had found a solution to this problem as I run into this scenario quite frequently? I also understand that it was posted 4-years ago, so this may not even be much of a memory.

 

[AMH80]

Hi Epst13n,

I was looking at this as well recently, and I found this link which was really useful;

A Dynamic Dependent Drop Down List in Excel | Excel Semi-Pro

Ideally I'd like to be able to have more than one dependent drop down, but using the table method currently works best for me, i.e. can add new info quite easily.

Cheers

Andy