Multiple drop down lists, all dependent of the same previous drop down list + Combination

Hello everyone,

**EDIT : See attachment for better understanding**

I have two problems, If you could help me solve them I would really appreciate :p


1. I've been working with dependent drop down lists recently, I figured out how to make dependant cascading drop down lists using DataAnalysis (=INDIRECT) and applying names to my lists. My problem is that this technique only let me make one list dependent of one other.

What I would like to do is have one list that influences many others (one list to rule them all).

For example, I am working with a list of "Fields" (Ex: field 1, 2 and 3) and each field have different lists of "parameter", "model" and "method". To make it clear, the methods and parameter are dependent of the field, but the methods and parameters are independent regarding each others.

How could I make it so that choosing a certain field in a drop down list would restrict the parameter drop down list AND the methods AND the model drop down list ?


2. In this same scenario, I have Different fields that restrict different parameters . I would like to be able to select more than one field, and then the dependent drop down list would contain the selections of the two fields at the same time, let it be understood that I dont want to select the fields on the same drop down list, but on different drop down lists.(same thing for model and methods)


I Hope all this is possible
Thank you,
Justin

I think I can help you with the problem no 1, by using vba, but you'll need to change your data layout. The data should be in one table with 3 column, something like this:

Excel 2013 32 bit

	A	B	C
1	field	column 2	column 3
2	Bone health	Model	Meta-analysis
3	Bone health	Model	Astronauts
4	Bone health	Model	Clinical trial
5	Bone health	Model	Animal (specify in Comments)
6	Bone health	Parameter	BMD/BMC
7	Bone health	Parameter	Osteoclast and osteoblast
8	Bone health	Parameter	Bone Strenght
9	Bone health	Method	Computed*Tomography
10	Bone health	Method	Mechanical testing
11	Bone health	Method	Histomorphometry*
12	SANS and Vision	Model	Meta-analysis
13	SANS and Vision	Model	Astronauts
14	SANS and Vision	Model	Clinical trial
15	SANS and Vision	Model	Animal (specify in Comments)

Sheet: Sheet1

Let me know if you're interested in this method.

Yes I would really like to hear what you have to propose ! I still haven't figured it out.

Could you explain in more detail what you're trying to do by using an example?
let's say you inserted data in these 3 cells (which are in field column) like this:
Excel 2013 32 bit

	C	D	E
13	Field-1	Field-2	Field-3
14	Bone health	Digestion	Fatigue

Sheet: Tests

then say in these other 3 cells below, what do you want to have in their the data validation respectively?
Excel 2013 32 bit

	J	K	L
13	Model	Parameter - 1	Parameter - 2
14

Sheet: Tests

I don't understand which field to use as the criteria.
So for example, on J14 (Model), do you want (in its data validation) all items that are "Bone health" and "Model"? or "Digestion" and "Model"? or Fatigue and "Model"?

I think I've just solved a similar problem (maybe the same), without resorting to VBA or re-organising tables, although it did require extra tables. My problem was to have multiple drop down lists all based on the same initial drop down. The context is ordering sports clothing (jerseys, socks, hats etc). Each item type has a different list of sizes and also has a set of item names, so I need three columns:
1. Item type (from list of "Jerseys", "Hats", "Socks")
2. Item Size (from list specific to ItemType, e.g. Jerseys (S,M,L,XL,XXL), Hats (OneSize), Socks (L.XL)
3. Item Name (from list specific to ItemType, e.g. Jerseys "ProCut White", "ProCutGreen", Hats "Bobble", SportsCap" etc)
So both columns 2 and 3 need to offer lists based on column 1

To solve, I created named ranges in tables for each decision:
Attachment 659734
Attachment 659735
So that there is
1. a set of named ranges based on column 1 "ItemTypes" all named simply, e.g. JERSEYS as per the direct text in that column. This is for the different Size lists for each item type
2. a set of named ranges based on a modified column 1 "Item types", based on a naming convention of "ItemType" + "NAMES" for each list of specific items

Then, the data Validation rules are for each of the above cases:
1. Size choices (the usual usage of INDIRECT): DataValidation: List: =INDIRECT(B2)
Attachment 659738
2. Item Speicific (modified use of INDIRECT with string modifier to concatenate "NAMES" onto the original list to avoid duplicate name clash of named ranges for INDIRECT usage and to match the additional tables): DataValidation: List: =INDIRECT(CONCAT(B2,"NAMES"))
Attachment 659739

Hope this helps
Cheers
Iain