How to do interlinked Drop Down

[huztin]

I would like to do a do dual drop down. So say there is a list of people A, B, C, D, E...each like say colors red, blue, green. I woud like a dropdown1 to select the color, and then the dropdown2 would show only the names of people that like color selected in dropdown 1.

 

[jbeaucaire]

1) Groups of colors in column A
2) Names that go with the colors in column B

3) A named range called Colors that is the list of colors, in the example below the named range REFERS TO: E1:E3

4) Apply the data validation to G3:
Allow: List
Source: =Colors

5) Select RED from the G3 list prior to doing the next step.

6) Click on H3 (important for this to work)

7) Open the Insert > Name > Define and create a dynamic named formula called Names that
REFERS TO: =OFFSET(INDEX(Sheet1!$B:$B, MATCH(Sheet1!$G1, Sheet1!$A:$A, 0)), , , COUNTIF(Sheet1!$A:$A, Sheet1!$G1), )

8) When done, open the DV settings for H3 and use:
Allow: List
Source =Names

All done....

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Red	Bob			Red
2	Red	Sam			Green		List	List2
3	Red	Sarah			Blue		Red
4	Green	Tommy
5	Green	Bob
6	Green	Sandy
7	Green	Carl
8	Green	Phil
9	Blue	Carl
10	Blue	Sam
11	Blue	Phil
12
13
14
Sheet1

#VALUE!

 

[huztin]

Thank you for your response. I tried this as suggested with only difference that for item 7, i went to Formulas>Define Name as there was nothing under Insert

Doing this when i select Red in List1, it still gives me all the people name in List2 instead of just the people associated with List1

Thanks again for your time and help

 

[jbeaucaire]

1) What's the validation source for your List2 cell?

2) If it's a named function, what is the Refers To: definition for that named function?