cascading combo boxes...??? - Page 4
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 4 of 12 FirstFirst ... 23456 ... LastLast
Results 31 to 40 of 116

Thread: cascading combo boxes...???

  1. #31
    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: Match function

     
    Quote Originally Posted by tz62lm
    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.

  2. #32
    New Member
    Join Date
    Feb 2005
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

    Quote Originally Posted by 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).


    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

  3. #33
    Board Regular
    Join Date
    Apr 2002
    Location
    South Bend, IN
    Posts
    991
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  4. #34
    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

    Quote Originally Posted by UHsoccer
    ...
    Is it possible to have the lists on a different sheet than the dropdown selections?
    Yes, if they all have a name.

  5. #35
    Board Regular
    Join Date
    Apr 2002
    Location
    South Bend, IN
    Posts
    991
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #36
    New Member
    Join Date
    Sep 2003
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:
    Code:
    =INDIRECT(VLOOKUP(A1,LookupTable,2,FALSE))

  7. #37
    New Member
    Join Date
    Apr 2005
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  8. #38
    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 sameena
    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," ",""))

  9. #39
    New Member
    Join Date
    Jan 2005
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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-dep....htm#ExcelTips

    Cheers
    luc

  10. #40
    Board Regular
    Join Date
    Sep 2003
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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



    Quote Originally Posted by 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.

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