cascading combo boxes...??? - Page 8
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Page 8 of 12 FirstFirst ... 678910 ... LastLast
Results 71 to 80 of 116

Thread: cascading combo boxes...???

  1. #71
    New Member
    Join Date
    Feb 2008
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

     
    I have copied this formula and I get an error what says "The source evaluates to an error", any ideas??

  2. #72
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    11,044
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    You will get that message if the formula currently evaluates to an error. If you know that it will work once a choice has been made then it doesn't matter ( ignore the message, and see if everything operates as expected ).
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

  3. #73
    New Member
    Join Date
    Feb 2008
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    I have cleared the error, and a dropdown appears but now nothing, so what am I doing wrong??

  4. #74
    New Member
    Join Date
    Feb 2008
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Can someone send me a speadsheet with the basic USA/France INDIRECT function so I can see it please??

  5. #75
    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 jamiecgreen View Post
    Can someone send me a speadsheet with the basic USA/France INDIRECT function so I can see it please??
    Communicate your email thru a PM...

  6. #76
    New Member
    Join Date
    May 2008
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy 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.
    This is exactly what I need to do - I tried it and it's perfect. The only problem is I need it to work with proper combo boxes (from the Forms toolbar in Excel 2003). I noticed a number of people have said it doesn't work, but I'm hoping that since 2002 when they posted something may have changed? Even if it requires VB (though I know nothing about how to make that work and would need a lot of help!).

    Any help would be greatly appreciated!

    Jen

  7. #77
    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...???

    actually you can get it to work with comboboxes. link here:
    http://www.mrexcel.com/forum/showthread.php?t=318684
    it's the hardwork of various mvps and posters

    you can force =indirect into comboboxes by using define names. if you need more info on this, lemme know
    Last edited by Pukka; May 12th, 2008 at 03:24 AM.
    My hamster has gone to a land Far Far Away.

  8. #78
    New Member
    Join Date
    May 2008
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi Pukka

    Sorry - I don't see how that thread helps me? I ended up finding someone else's posting explaining how to do it, and while I've tried it and it works I still don't actually -understand- it!

    Can you help?

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


    ******** ******************** ************************************************************************>
    Microsoft Excel - ChangingCombo2.xls___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =


    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    1
    ******CodeCategoryData*Code
    2
    ******101AA500*Category
    3
    **39**102BA501*Data
    4
    ******103CA502**
    5
    **2$I$2:$I$20**104DA503**
    6
    ******105EA504**
    7
    ******106FA505**
    8
    ******107GA506**
    9
    ******108HA507**
    10
    ******109I***
    11
    ******110J***
    12
    ******111K***
    13
    ******112L***
    14
    *******M***
    15
    *******N***
    16
    *Named*ranges:****O***
    17
    *Master=INDIRECT(Sheet2!$D$5)***P***
    18
    *MyData=Sheet2!$K$1:$K$3***Q***
    19
    *******R***
    20
    *******S***
    21
    ***********
    Sheet2*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    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

  9. #79
    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...???

    try this sample I made, see if you understand.
    http://www.geocities.com/gjfeng/comboboxSample.xls
    My hamster has gone to a land Far Far Away.

  10. #80
    New Member
    Join Date
    May 2008
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

      
    Nope - the combo boxes don't work?

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