Form Combobox Combo

[deepheat]

Greetings Fellow Excel'ers

I have two form-type Combo Boxes ("List A" and "List B") on a sheet and I would like the input-range of the second to be dependant on the value of the first.

My lists are as follows...

List A       SubList-1   SubList-2   SubList-3
SubList-1    Apples      Blue        ****
SubList-2    Bananas     Pink        Harry
SubList-3    Pears       Red         Tom
                         Yellow

I would like to be able to select an item from combo box "List A" and then have combo box "List B" have the appropriate list as shown above. So if I select "SubList-3" from "List A", "List B" would have "****", "Tom" and "Harry" as its inputs.

I've tried defining a name (cmboOthers) using...

=CHOOSE(L2,OFFSET($N$5,0,0,COUNTA($N:$N)-3,1),OFFSET($P$5,0,0,COUNTA($P:$P)-3,1),OFFSET($R$5,0,0,COUNTA($R:$R)-3,1))

(List A value held at L2 while sublists are in columns N, P and R and start from row 5)

...but when I attempt to add the name cmboOthers into the "Input range" field of the combo box "Format Control" dialogue box I receive a pop-up saying "Reference is not valid". At this point I get stuck!

How do I make this work?

I'm using 2007.


Many Thanks in advance

dp