• 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.

Changing Drop Down Menus

ahmetk

ahmetk

Member
Dear Excel Gurus,


I am not sure whether if I have titled my search correct but please allow me to explain what I am trying to do.


You know how on certain web pages while you are doing a search for a certain item, it first starts with a main drop down menu ex: "car & real estate" and as you pick from the list depending on your choice new drop down menus appear next to it with related new options.


Please see a sample excel file for details:


http://speedy.sh/7PGft/categorize-search.xlsx


The file I have attached is a small sample of what I am trying to achieve.


What I am trying to do is:

Excel should have a box with a list at the beginning: Say in this case "Car & Real Estate".


If I pick "Car" from the list a new list should appear next to it with "SUV, Compact and Luxury", if I pick "SUV" a new list should appear next to it with "4 wheel drive & 2 wheel drive."


If the user picks "Compact" from the list a different set of choices shall come: "4 door & 2 door".


The list can be expanded.


And when all choices are done at the bottom a full line with entire choices shall appear. For instace: "Car" "Compact" "2 wheel drive"


Is this possible?


I have tried to do it with pivot tables but was unable to, with dropdown menus I couldn't set different menus to go with my choices.


Little lost.


Any comments?


Kind regards.


Ahmet K.
 
Having done some more search I have found the following information:


http://chandoo.org/wp/2008/11/25/advanced-data-validation-techniques-in-excel-spreadcheats/


But in this excel first list and corresponding data is manually fixed.


Meaning if I add a new item under the first list it is not recognized.


My study shall be dynamic.
 
Dear Gurus,


All of the data validation or "Changing Lists Based on Previous Selection" I have found is limited to 2 list.


I would like to expand these to 3, 4 or even more lists but can't figure out how to set the correlation with previous list add bring the related "data validation list".


kind regards.
 
hi,


see this topic : http://chandoo.org/forums/topic/combo-box-dropdown-refer-to-another-combo-box


and as debra pointed out, try and search in google for excel dependant drop down list if needed.


and for automatic list you can search excel dynamic range
 
Dear Koi,


Thank you very much for your kind response but all of the pages and examples I have checked based on two data: You choose one and the next drop down menu changes based on your previous selection.


Fine but I also want to have a third menu that should change based on the second selection I have done.


For instance the first selection is "fruit" or "vegetable". If fruit is picked than a list of fruits come. That's it.


I wish to have a third (even fourth or fifth list) where if "apple" is picked after "fruit" selection a new list should also come with a new selection list ex: "green" or "red".


That's what I am trying to achieve.


Kind regards.


AK
 
In the following web page the reader also searches the same thing as I am.


http://excelsemipro.com/2012/04/a-dynamic-dependent-drop-down-list-with-a-horizontal-table-reference/


"This is great thank you very much. I was wondering, however, is there a way to have another branch. For example, I have a table of categories, these categories have sub-categories and a few of these have sub-sub-categories, like DesignDrawingsPlanning Drawings or DesignChecklistsList 2. Is there a way I could use a dynamic dropdown for more branches, so I could select Design in the first cell, then Drawings in the second then Planning Drawings in the third?"


Unfortunately there is still no reply.
 
Dear Excel Gurus,


I have trying to create dynamic dropdown menus with 3 or more branches for the last 3 days but all of the samples I found it limited to two.


Does anyone have an idea on how to solve this matter?


Kind regards.


AK.
 
It can be done in VBA populating the combo box with specific data depending on the value of another combo box etc - this would have no limitations to the number of consecutive combo boxes.
 
good day ahmetk


Try this site a first class course in learning multi dropdown data validation.


http://www.contextures.com/xlDataVal02.html
 
Dear Bobhc,


Thank you very much for your kind response & link. I have finally done it. :)


Kind regards.


AK
 
Good day ahmetk


Thanks for taking the time to give some feed back, glad you have sorted your problem.


I have placed a word document in my Dropbox folder that explains Dynamic lists so that you can then add and delete items from your dropdown boxses without having to reset the lists each time


https://dl.dropbox.com/u/75495784/Dynamic%20Excel%20Named%20List%20Grows%20Automatically.docx
 
Dear All,


I have completed my draft dynamic dropdown menu with multiple branches where you can keep selecting from different menus and can go on forewer.


For the ones who are interested please see the below link:


http://speedy.sh/Gj6ZH/cloth-finder.xls


Kind regards.


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