cascading combo boxes...??? - Page 9
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Page 9 of 12 FirstFirst ... 7891011 ... LastLast
Results 81 to 90 of 116

Thread: cascading combo boxes...???

  1. #81
    Board Regular Pukka's Avatar
    Join Date
    May 2008
    Posts
    172
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: cascading combo boxes...???

     
    sorry posted the wrong file. here it is

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


    will post the steps in a sec....
    Last edited by Pukka; May 12th, 2008 at 04:22 AM.
    My hamster has gone to a land Far Far Away.

  2. #82
    Board Regular Pukka's Avatar
    Join Date
    May 2008
    Posts
    172
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Re: cascading combo boxes...???

    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!
    Last edited by Pukka; May 12th, 2008 at 05:14 AM.
    My hamster has gone to a land Far Far Away.

  3. #83
    Board Regular Pukka's Avatar
    Join Date
    May 2008
    Posts
    172
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: cascading combo boxes...???

    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?
    Last edited by Pukka; May 12th, 2008 at 05:25 AM.
    My hamster has gone to a land Far Far Away.

  4. #84
    New Member
    Join Date
    Sep 2008
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: cascading combo boxes...???

    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

  5. #85
    New Member
    Join Date
    Apr 2009
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: cascading combo boxes...???

    Thanks Aladin for detailed example. very useful

  6. #86
    New Member
    Join Date
    Apr 2009
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: cascading combo boxes...???

    Brill,

    Tanks for your help

  7. #87
    New Member
    Join Date
    Feb 2010
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: cascading combo boxes...???

    Quote Originally Posted by Aladin Akyurek View Post
    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!

  8. #88
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,786
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: cascading combo boxes...???

    Quote Originally Posted by Vegemite Kid View Post
    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!.
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #89
    New Member
    Join Date
    Feb 2010
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: cascading combo boxes...???

    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.

  10. #90
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,786
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: cascading combo boxes...???

      
    Quote Originally Posted by Vegemite Kid View Post
    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?
    Assuming too much and qualifying too much are two faces of the same problem.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com