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

Drop down list in Hierarchical format

W

webdog

New Member
I am looking at a way to display drop down list in a hierarchical format and allow the user to pick a leaf node as a valid entry. It is not necessary to have the nodes expandable or collapsible, but it will help the user to navigate easily through several hundreds of entries in the list.


For example: When the user wants to select a product, the drop down list should appear as follows:


Cat A

|-----Cat AA

|----Cat AAA

|---PROD A1

|---PROD A2

|

Cat B

|-----Cat BB

|----Cat BBB

|---PROD B1

|---PROD B2


The standard drop down supports a list format so hierarchical or Tree view is out of question. Has anyone in the forum tried this before? Any pointers will be greatly appreciated.


Thanks,

Webdog
 
Hi, webdog!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s), maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


Or if you want you can go directly to this link, where I set a 2-level dependency which I think it's not hard to adapt to n-level of hierachy:

http://chandoo.org/forums/topic/data-validation-list-automatically-shows-the-first-item-in-the-list


Regards!


PS: WebDog bits the bits? I hope it doesn't :)

Regards!
 
Hi Webdog,


As you have already got your WELCOME KIT, now time for Bon Appétit,


If I am not wrong then you are searching for something like this..


Sorry, I tried to search from where I have downloaded it, so that I can send you the URL, but failed. So just pass my Thanks to ANDY if you found the link.. :)


https://dl.dropbox.com/u/78831150/Excel/hlist.xls


Regards,

Deb


EDIT : Yep.. got it..

http://www.andypope.info/vba/hlist.htm
 
@Debraj Roy


Hi!


If you steal... cofff cofff... borrow something without permission... cofff cofff... download something... ok, was that!... from... cofff cofff... oh! that's the problem, you don't know that...


Maybe this helps:

http://www.mrexcel.com/forum/excel-questions/205873-building-hierarchy-threeview-visual-basic-applications.html

2nd post, 2nd link:

http://www.andypope.info/vba/hlist.htm


Regards!


PS: I used this secret technique for revealing the hidden source:

http://www.google.com.ar/#hl=es-419&output=search&sclient=psy-ab&q=hlist.xls+andy+pope+excel+hierachical&oq=hlist.xls+andy+pope+excel+hierachical&gs_l=hp.3...4127.4127.0.4950.1.1.0.0.0.0.245.245.2-1.1.0...0.0...1c.a6T3DW-z3Ys&pbx=1&bav=on.2,or.r_gc.r_pw.r_qf.&fp=e6368c1176e4ea90&biw=945&bih=1013

I think you could omit ".ar" and maybe the "hl=es-419" unless you want it in Spanish.


PS2: BTW, nice article, gonna look at it. Thank you.
 
@SirJB7 @Debraj Roy

Thanks for the prompt replies. I recently came across chandoo.org and have been enjoying various posts. This place is truly amazing and hats off to you guys.

I followed your suggested links and think it comes close to what I was looking for. Except I am new to VB programming and need to figure out how to adopt object ajpHList in a drop down list. Wish there was a standard object in Excel to do this...anyway, I really appreciate you guys showing me the way and I will try to take it from here....
 
@Webdog... Since you just need a display list and selection of node values, I think the solution shown by SirJB & Debraj might be overkill. I suggest using a simple drop down list that is formatted to look like a tree and apply conditional formatting to alert when a non-node value is selected.


See this file for a demo of what I mean. You can extend the data section to include any number of items. Just provide parent node information and the tree will be constructed.


http://img.chandoo.org/playground/heirarchial%20list.xlsx
 
@Chandoo....Such a simple and elegant solution....a masterpiece from a genius who I think is from a different planet. Thanks a lot and I will continue to follow/participate your great site which has become part of my daily reading ritual.


Kind regards,

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