Dynamic dropdown

[mac_see]

Book1
	A	B	C	D	E
1				Parameter1	SubPara1_1
2					SubPara1_2
3					SubPara1_3
4					SubPara1_4
5					SubPara1_5
6
7				Parameter2	SubPara2_1
8					SubPara2_2
9
10				Parameter3	SubPara3_1
11					SubPara3_2
12					SubPara3_3
13
14				Parameter4	SubPara4_1
15					SubPara4_2
Sheet1

I have a dropdown box in cell A1 that has 4 values:
Parameter1
Parameter2
Parameter3
Parameter4

I want a dropdown in cell B1 which should be dynamic, it should depend on what is selected in cell A1. If user selects Parameter1, it should look up the corresponding sub-parameters from column E and put it in the dropdown list.

The above is sample data, I have more than 10 main parameters and sub parameters can be 2 to 25+

Maxi

 

[SteveO59L]

Have a look at Aladin' solution, here

http://www.mrexcel.com/board2/viewtopic.php?p=154#154

 

[mac_see]

Thanx for your help Steve. I tried that example and I got an error which I have posted in that article.

Thank you once again

 

[bosco_yip]

.....D...................E..........
Parameter1......SubPara1_1
Parameter1......SubPara1_2
Parameter1......SubPara1_3
Parameter1......SubPara1_4
Parameter1......SubPara1_5

Parameter2......SubPara2_1
Parameter2......SubPara2_2

Parameter3......SubPara3_1
Parameter3......SubPara3_2
Parameter3......SubPara3_3

Parameter4......SubPara4_1
Parameter4......SubPara4_2

1. Try to fill up the Column D datas as above

2. Let cell A1 have a dropdown box of values: {Parameter1, Parameter2, Parameter3, Parameter4 }

3. Cell B1 >> Data Validation >> Choose : List and enter the following formula :
=OFFSET($E$1,MATCH($A1,$D:$D,)-1,,COUNTIF($D:$D,$A1))

Hope that helps

 

[mac_see]

I get the same error

The source currently evaluates to an error. Do you wish to continue?

I am using Excel XP
Microsoft Excel 2002 (10.6501.6626) SP3

 

[mac_see]

I don't know what went wrong but it is working now

Thanks