• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Linking Combo Boxes

A

ashl

Member
Hi guys,


I have a problem. I'm trying to link 2 combo boxes so that when I select a topic in the first combo box I should be able to go to the second and it will give me the items under that product.


With data validation u can reference the first drop down box by using the indirect formula in the second (if you have used named ranges).


How do I do this with combo boxes? I have set my data out so that it is broken down into named tables. I need help plz to get this working with the second combo box if possible without using any code.


Any help is greatly appreciated,


Thanks :)
 
I'm assuming this is a combo box from the Forms menu (rather than ActiveX) since you said there's no code.


With Forms control, the linked cell will generate a number. You'll need to take this number and plug it into an INDEX function to get the correct range for the dependent combo box.


So, the regular way would have the data validation using a formula like:

=INDIRECT(A2)


With a combo box, you'll need to create a Named Range (let's say it's named Choice2) with this formula:

=INDIRECT(INDEX(FirstComboBoxChoice,FirstLinkedCell))


Then, in the source range for the 2nd combo box, simply put

Choice2
 
Hi Luke,

Thanks for your speedy reply. Sorry yes I meant forms control.


At the moment I have made a table with all the products, then made seperate tables for each product to include the items. Do I need to change this to make this work?


Sorry I think im coming down with a cold my brain is slowly going on strike.


Thanks :)
 
No, you shouldn't need to. I am assuming that each table has been given an assigned name, and you've got a list somewhere of all these names that are being fed into the first combo box.


Actually, thinking about this more, you can skip the INDIRECT function since INDEX lets you designate an area arguement. Let's say you have three tables named

Code: 
Fruits, Vegetables, Dessert

First combo box has these choices listed, and the linked cell is going to be either 1, 2, or 3. Named Range formula can then just be:

=INDEX((Fruits,Vegetables,Dessert),,,LinkedCell)


Either formula will work just fine I believe.
 
Hi Luke,


Sorry my brain is definitely not working. I've done something wrong cause my second combo box shows me only one item from fruits but when i change the option from the first combo box it shows me the second item in fruit in the second combo box.


Could u tell me plz what I'm doing wrong please.

Ive used the index formula and named it Choi2. I couldnt get a value for the first formula.


https://www.box.com/s/0wlkd7zrm7m80aqmbbv9


Thanks :)
 
Afraid I can't access uploaded workbooks from my location. =(

Taking a guess, I'd check the parenthesis and comma placements in your formula. Note that all the table names need to be contained within a set of parenthesis so that they are all included as the 1st argument in the INDEX function.
 
Hi, ashl!


Thanks (or should I say due?) to my friend Luke M's usual issue CASFFML, I downloaded your workbook and did this:


a) created a new named range ChoixDeux :) defined as:

=INDIRECTO(INDICE(Product;Tabelle1!$C$18)) -----> in english: =INDIRECT(INDEX(Product,Tabelle1!$C$18))


b) assigned ChoixDeux to 2nd combo box source


... et voilà! c'est tout...


Give a look to this file:

https://dl.dropbox.com/u/60558749/Linking%20Combo%20Boxes%20-%20LinkComboBox%20%28for%20ashl%20at%20chandoo.org%29.xlsx


Hope it helps, just advise if any issue.


Regards!


PS: ou est-ce que je devrais dire à bientôt?

PS2: (for the community) or should I say see you soon?
 
Thanks SirJB7. As usual, you're here to bail me out. Beginning to wonder if I should start tagging posts with "CASFFML" so that you can easily find me. =P
 
@Luke M

Hi!

Wasn't CASFFML you actual name indeed?

Regards!


@All

Hi!

CASFFML stands for can't access shared files from my location... just in case an unaware reader steps into it.

Regards!
 
Hi Luke and SirJB7,


Unfortuantely I am now CASFFML and can't access the file, but thanks for your help I'll check it out tonight and let u know.


Big thanks again :)
 
Thanks Guys - it works!


It took a while for me to figure out that it doesnt like spaces between words which is why it wouldn't work at first.


Big thank u again for your efforts :)
 
Hi, ashl!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top