Excel macros drop down lst

QuestionEdit

i have a doubt regardingmacros. suppose i have a cell with a drop down box with options cricket,football,basketball in cell A2. if i select football in cell A2 i need to get an drop down list in cell B2 with options Spain, Italy,germany.and on selecting Spain in cell B2 i need to get a drop down box in cell C2 with the names of the players. Similarly if i select cricket in cell A2 i get a drop down box in cell B2 with options Australia,England, India. and on selecting any 1 of this i get a dropdown box in C2 with players name. please do help me. how can the programming be done.

AnswerEdit

On a blank sheet, enter in range D1:D6

cricket cricket cricket football football football

in E1:E6 enter

Australia England India Spain Italy Germany

In A1, create incell dropdown. In the Data Validation popup, Settings tab, choose Allow: List, and for Source:, enter

cricket, football.

In B1, create incell dropdown. In the Data Validation popup, Settings tab, choose Allow: List, and for Source:, enter

=OFFSET($E$1,MATCH($A$1,$D:$D,0)-1,0,COUNTIF($D:$D,$A$1),1)

Now, when the A1 dropdown is cricket, B1 dropdown will have options of Australia, England, India. If A1 is football, then B1 options are Spain, Italy, Germany.