cascading combo boxes...???

[Gurps]

is there any way in which I can get the selection from one combo box to influence the second?? In other words, The second combo box is dependant on the first.
I am using Excel XP.

any help would be appreciated.
cheers

 

[Emmanuel]

Great Stuff!
I'd like to push it forward:
After you choose a country (USA) and a city, you may want then to choose another country in A1. The only thing is that the city from USA is still in the other cell.
Is there any (dynamic?) macro in order to delete the already choosen city when you change the selected country.
Regards
Emmanuel

 

[JohnnyW]

Many thanks. This is the first time I have used this service and will certainly do so again.

 

[mgana]

Is there a way to get this method to work with non-adjacent named ranges, like from an existing database?

 

[Zack Barresse]

Is there a way to get this method to work with non-adjacent named ranges, like from an existing database?

as far as i know, named ranges will only work within it's own workbook. they can work cross-sheets which is very nice. maybe copying one sheet to another workbook and accessing from there? many possibilities.

 

[Aladin Akyurek]

Is there a way to get this method to work with non-adjacent named ranges, like from an existing database?

How do you mean?

 

[chenchanjuan]

'Is there any way in which i can get the drop down arrows to stay there permanently? because i need a visible menu option."

is it possible to tell me how to do it in VBA?

Thanks!!
Cathy

 

[Zack Barresse]

try looking at making a combo box.

 

[Ekim]

Gurps,

You could do this with “real” combo boxes.

See the exhibit:
Your data is in sheet2, columns G, H and I.
It is assumed that the max number of rows for the data is 20 (column H).

ChangingCombo2.xls
	A	B	C	D	E	F	G	H	I	J	K
1							Code	Category	Data		Code
2							101	A	A500		Category
3			3	9			102	B	A501		Data
4							103	C	A502
5			2	$I$2:$I$20			104	D	A503
6							105	E	A504
7							106	F	A505
8							107	G	A506
9							108	H	A507
10							109	I
11							110	J
12							111	K
13							112	L
14								M
15								N
16		Namedranges:						O
17		Master	=INDIRECT(Sheet2!$D$5)					P
18		MyData	=Sheet2!$K$1:$K$3					Q
19								R
20								S
21
Sheet2

Step 1
In column K, list your data headings and name this list as “myData”.

Step 2
D3: put the formula -
=$C$3+6

From the Forms menu, create a Combo box that covers cells C3:D3.
The input range is “MyData” (no quotes)
Cell link is $C$3.

By covering cells C3:D3 with the Combo Box, you are hiding the formula in D3 and the link cell in C3.

Step 3
D5: put the formula -
=(ADDRESS(2,D3) &":"&ADDRESS(20,D3))

Note that the formula in D5 is linked to the formula in D3 (step 2 above).

Step 4

From the Insert menu | Name | Define, create a named range called “Master” that refers to “=INDIRECT(Sheet2!$D$5)” (no quotes).

Step 5

From the Forms menu, create another Combo box that covers cells C5:D5.
Input range is “Master” (no quotes)
Cell link range is $C$5.

Again, by using the second combo box to cover cells C5:D5, you are hiding the link cell in C5 and the formula in D5.

That’s it.

Access the first combo box and make a selection. The second combo box will reflect your choice from the first combo box.

HTH

Mike

 

[djd79]

Going back to the example give by Aladin at the beginning of this post…..

What if I have three lists? The combination of the first 2 lists will restrict the data that I am able to select in the third list?

I have 12 items in the first list, 26 items in the second list and about 250 items in the 3rd list.

Thanks,
Daniel

 

[Aladin Akyurek]

Going back to the example give by Aladin at the beginning of this post…..

What if I have three lists? The combination of the first 2 lists will restrict the data that I am able to select in the third list?

I have 12 items in the first list, 26 items in the second list and about 250 items in the 3rd list.

Thanks,
Daniel

Can you give a sized down set that shows the interdependence you're looking for?