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

 

[Aladin Akyurek]

Re: Match function

thanks for the info. but I'm have 4 columns of data validation. each one is dependent on the previous. And the issue is when any of the data values is duplicated, Excel doesn't handle this well with the formula I'm using with INDIRECT and MATCH.

Maybe a bit more information would help.

 

[cbaisden01]

Using Ekim's example here, does anyone know how to create a third list that displays choices driven by the selection made in the second drop-down.

For instance, if we are using these options:

MYDATA = Dog, Cat, Bird

DOG = Maltese, Poodle
CAT = Tabby, Siamese
BIRD = Parrot, Eagle

...up to this point, Ekim's formula are perfect. But now I would like to add another set of qualifiers:

DOG STATUS = Male, Female
CAT = Long Hair. Short Hair
BIRD = Flying. Clipped Wings

See what I am getting at?
Let me know!

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).


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

 

[UHsoccer]

This is the first time I actually understand the "INDIRECT" function. Thanks Aladin, your detailed instructions were perfect!.

Is it possible to have the lists on a different sheet than the dropdown selections?

 

[Aladin Akyurek]

...
Is it possible to have the lists on a different sheet than the dropdown selections?

Yes, if they all have a name.

 

[UHsoccer]

Thanks, I was adding the sheet name to the list name!

 

[JTBenson]

If your range name is different than the value in the other validation box, you can use a lookup table to match the value and the range name. Here is the formula:

=INDIRECT(VLOOKUP(A1,LookupTable,2,FALSE))

 

[sameena]

cascading combo boxes

I have just used the method to create indirect links - this works really well if you have a single entry in each cell. How about if I had multiple words in a cell for example in the first column I had a country called New Zealand and then in the next column I had towns in new zealand.

At the moment the first column countries has an entry with 2 words in ie "New" and "Zealand" - when you pick this from the list the dependent cell with the formula indirect is blank.

How do you solve this problem.

Thanks

 

[Aladin Akyurek]

Re: cascading combo boxes

I have just used the method to create indirect links - this works really well if you have a single entry in each cell. How about if I had multiple words in a cell for example in the first column I had a country called New Zealand and then in the next column I had towns in new zealand.

At the moment the first column countries has an entry with 2 words in ie "New" and "Zealand" - when you pick this from the list the dependent cell with the formula indirect is blank.

How do you solve this problem.

Thanks

Name the list of towns NewZealand.

Let A2 house the list of countries.

Invoke a sublist/dependent list with:

=INDIRECT(SUBSTITUTE(A2," ",""))

 

[luc38luc]

Hi guys,

If you want two combo box and the second will depend of the first one , I suggest you download this free demo. It is pretty nice and i have used it to do my own projects and it s working just fine
The links is

http://www.ozgrid.com/News/excel-dependent-lists.htm#ExcelTips

Cheers
luc

 

[Steve Rosenberg]

Just found your formula...you are the man!
Thanks,
Stevfe

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.