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

 

[Pukka]

sorry posted the wrong file. here it is

http://www.geocities.com/gjfeng/comboBoxINDIRECT.xls


will post the steps in a sec....

 

[Pukka]

File: http://www.geocities.com/gjfeng/comboBoxINDIRECT.xls

***Naming***
1) Sheet2: name 'select fruits' column as fruits, name 'Select Veggie' as veggie
2) Sheet2: name 'List' as list
***NOTE** The names in the list is sensitive! See example below

***Validation List***
1) We'll select F4 as list1 and G4 as list2
2) select F4 then go data> validation then settings>allow>list then source: =list
3) select G4 then go data> validation then settings>allow>list then source: =INDIRECT(F4)
***Step 3 is optional!***

***Comboboxes***
In this example we'll use control toolbox Combobox
1) create 2 combobox anywhere
2) combobox1: click on design mode(control toolbox), rightclick on combobox1> properties and look for 2 fields: LinkedCell, ListFillRange
3) combobox1: LinkedCell put $F$4(do not put just F4, $ needed!)
4) combobox1: ListFillRange put list
5) go insert>name>define> name: comboList source: $F$4
then click add
**details of Y? Step5 below**
6) Combobox2: click on design mode(control toolbox), rightclick on combobox2> properties and look for 2 fields: LinkedCell, ListFillRange
7) combobox2: LinkedCell put $G$4(do not put just G4, $ needed!)
***step 7 is optional!***
8) go insert>name>define> name: comboList source: =combolist
then click add
9) go again to insert>name>define> name: comboList
source: =INDIRECT($F$4)

**Reasons for step 5: The combobox2 ListFillRange Properties by default will not accept =INDIRECT formulas. To force it to accept, we have to type something else at the source first (like $F$4). After you force the ListFillRange to accept the name, Go back to the source and put the =INDIRECT Formula.



If you name column B as fru and Column C as veg,
your list column (Column A) should be:
List - fru, veg instead of: List - fruits, veggie

Column B 'Select Fruits' named as fruit

Column C 'Select Veggie' named as veggie

Guys if it is still not clear, please let me know!

 

[Pukka]

I'm unable to edit the above post,
Step 8 should be:
combobox2: ListFillRange source: =combolist

just in case:
***naming*** is done in sheet 1
***Validation list*** is done in sheet 2
***ComboBoxes*** is done in sheet 2


sneakyseal, does it work for you?

 

[CWangOSU]

Here's a different iteration of the dual validation issue that's been talked about in this thread any help is appreciated!

I was curious if it was possible to create a dropdown based on a group of ID's from one row that is dependent on values from two other rows.

For example:
I have two tables with the descriptions below.

The ID tab would have three columns:
A) Advertiser ID (Manually fill out)
B) Creative ID (Manually fill out)
C) Image or Flash? (Static Dropdown)

The Image tab would have 3 columns:
A) Advertiser ID (Dropdown of all ID Tab Column A)
B) Image Creative ID (Dropdown of possible ID's from ID tab column B that are of the same Advertiser ID and are Image.)
C) Flash Creative ID (Dropdown of possible ID's from ID tab column B that are of the same Advertiser ID and are Flash.)

My issue is creating the Column B and C columns in the Image tab which are created from a list of Creative ID's (ID tab column B) and is dependent on the Advertiser ID that matches between the two tabs and what was an Image in the ID Tab.

Thanks again in advance.

-Chris

 

[nats_ry]

Thanks Aladin for detailed example. very useful

 

[seanoace]

Brill,

Tanks for your help

 

[Vegemite Kid]

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.

Bumping a v old post.

My first list appears fine but when I choose the second, I get an error message #VALUE! What does this mean? Thanks!

 

[Aladin Akyurek]

Bumping a v old post.

My first list appears fine but when I choose the second, I get an error message #VALUE! What does this mean? Thanks!

If you are referring to a set up of your own, try to post the definitions of your lists and the formula that evaluates to #VALUE!.

 

[Vegemite Kid]

Okay as follows:

In cells B14 and B15 I have USA and FRANCE When I highlight both, the name comes up COUNTRIES but not if I click on them individually. Is that right?

In cells C 14 - 16 I have 1 2 3 and this is named USA. In Cells D14-16, I have values a b c Ditto to the above re when I highlight both, the name comes up USA and FRANCE but not if I click on them individually.

In cell B19, I have chosen Data > Validation > List and then Allow List and Source=COUNTRIES In C19, (the problem), I have Allow List and then Source=INDIRECT(B19) giving me the error of #VALUE!

Thanks.

 

[Aladin Akyurek]

Okay as follows:

In cells B14 and B15 I have USA and FRANCE When I highlight both, the name comes up COUNTRIES but not if I click on them individually. Is that right?

In cells C 14 - 16 I have 1 2 3 and this is named USA. In Cells D14-16, I have values a b c Ditto to the above re when I highlight both, the name comes up USA and FRANCE but not if I click on them individually.

In cell B19, I have chosen Data > Validation > List and then Allow List and Source=COUNTRIES In C19, (the problem), I have Allow List and then Source=INDIRECT(B19) giving me the error of #VALUE!

Thanks.

Activate Insert|Name|Define.
Choose COUNTRIES.

What does the Reffering to box display?

Choose USA.

What does the Reffering to box display?

Choose FRANCE.

What does the Reffering to box display?