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

 

[Juan Pablo González]

If you could use Data Validation, I posted a Tip of the Day that explains this.

If you need to do this using "real" comboboxes, then, post again.

Tip of the Day for:
Saturday, January 26, 2002

 

[Gurps]

if I use the data validation method, how do I lay out the table of data??

I would prefer to use "real" combo boxes.Would you be able to tell me how to do this method as well?
thank you

 

[Aladin Akyurek]

On 2002-02-17 07:20, Gurps wrote:
if I use the data validation method, how do I lay out the table of data??

I would prefer to use "real" combo boxes.Would you be able to tell me how to do this method as well?

I don't believe the ComboBox would suit your purpose. The "Input range" appears not to like INDIRECT formulas.

As Juan suggested, it's more convenient to use Lists thru data validation.

The method is as follows:

Enter in some column what follows:

{"USA";"FRANCE"}[ That's, enter USA in a cell, then FRANCE in the next cell down ]

Select these cells, go to the Name Box on the Formula Bar, and type COUNTRIES followed by enter.

Enter in a column next to COUNTRIES:

{"New York";"Pittsburgh";"Los Angeles";"Boston"}

Name this range of cells USA via the Name Box as described above.

Enter in a column next to USA:

{"Paris";"Nice";"Toulon"}

Name this range FRANCE.

Just to see how this works,

activate A1 in some worksheet in the same workbook;

activate Data|Validation;

choose 'List' for 'Allow';

enter as 'Source' the formula:

=COUNTRIES

click OK;

activate another cell in the same worksheet, say, C1;

activate Data|Validation;

choose 'List' for 'Allow';

enter as 'Source' the formula:

=INDIRECT(A1)

click OK.

Now you have two lists of which the 2nd depends on the selection from the 1st.

 

[Guest]

cheers mate for the help. I've got that working. Is there any way in which i can get the drop down arrows to stay there permanently? because i need a visible menu option.

thanks again

 

[Aladin Akyurek]

On 2002-02-17 10:32, Anonymous wrote:
<snip>
Is there any way in which i can get the drop down arrows to stay there permanently? because i need a visible menu option. <snip>

As far as I know, there isn't a way to do it, unless someone can do it in VBA.

It isn't much, but you can color the cells, put borders around them, etc. to make them stand out.

 

[Guest]

thanks for all your help!!!!! much appreciated!!

 

[simonf]

Man, that INDIRECT is ssssssweet!

 

[colleen]

The indirect way is excellent..but now a poser say i choose from the first column usa and then a value )say 20) from the second..in the second worksheet I want the value to be subtracted from USA which is also in the second sheet..how??? col

 

[Aladin Akyurek]

On 2002-08-28 22:03, colleen wrote:
The indirect way is excellent..but now a poser say i choose from the first column usa and then a value )say 20) from the second..in the second worksheet I want the value to be subtracted from USA which is also in the second sheet..how??? col

I don't think I understand... Care to elaborate a bit more?