Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    validation list grouping? (IE 6.0)

    In excell I'm creating a database which is using the data validation drop-down list command on one workbook from a list on another workbook. I have the list defined as a name so that I can continually add to the list whenever I have to. The only problem is that this list will eventually contain thousands of string characters. Although I am the person creating this database I will not be the only one using it. So I was wondering - is there a way to put these characters in groups or sublists so that it would be easier for anyone to find a certain character? The list I am making consist of different parts making up different circuits. So theres parts like relays, breakers, and transformers - and within those parts there are different kinds. I guess I'm thinking of a list that works like the start menu whereas I can just go to the drop-down list and pick a character from a sublist which is branched off of a primary list. Is there a way of doing something like this? Or what would be the best way to accomplsih this? Thanks in advance - looking forward to your responce.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation list grouping? (IE 6.0)

    The only way (with data, validation) I would know of is to split the list into sub-lists, so the user first selects the main category, then sub-cat1, then sub-cat2 etcetera.

    If you're willing to go for programming, one could use a userform with a treeview control.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation list grouping? (IE 6.0)


  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: validation list grouping? (IE 6.0)

    Be aware of the downside to this method.
    Choose a Main category
    Choose sub category list (based on main category)
    All is Fine and data is VALID.

    Now change main category
    SubCategory is now not a member of the main category as validation does NOT warn that the item is NO LONGER VALID. It only checks on data entry.

    If that is a problem, multiple combo/list boxes might be another answer other than treeview.
    Steve

  5. #5
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation list grouping? (IE 6.0)

    I went to that contextures website and it help-out alot. Thanks. I just heard of this website today and I've been trying to solve this problem for about a week. Good lookin out!

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation list grouping? (IE 6.0)

    Hi Jan

    I've never used the TreeView control before. The XL 2000 help is very sparse on it. How exactly might one use it?

    In particular, I maintain an ongoing catalog of products with a caterory heirarchy. Difficulty has always been that some products have few sub-categories e.g. Christmas -> Tinsel, while others run quite deep
    e.g. Stationary -> Notebooks -> Hard Cover -> A4 -> Ruled
    This non-uniformity in this part of the product data (the "categorizing" information) has often had me considering a tree structure, but I had/have no idea of how to do it in XL. Any ideas on this appreciated.

    Alan

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation list grouping? (IE 6.0)

    There is a sample file here
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation list grouping? (IE 6.0)

    Thanks Hans. (Edited) <- Should read Jan :-) Time for bed!
    Fast work! :-)

    Alan

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: validation list grouping? (IE 6.0)

    I *know* that all Dutchmen look the same, but we *do* have different names! Credits to Jan Karel for this one, please, I am innocent.

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation list grouping? (IE 6.0)

    <hr>Fast work!<hr>
    I'm not always slow <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation list grouping? (IE 6.0)

    Well, what with the long hours lately, the multitude of Dutch expertise in this forum, "adjusting" to the presence of a Dutch cricket team on the international scene (without quite the same degree of expertise <img src=/S/grin.gif border=0 alt=grin width=15 height=15>) and the fact that I was also exchanging messages with (different) Jan, Hans and Henk on another forum, I'll excuse myself for the confusion on this occassion <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>.

    I was hoping to correct the error before anyone spotted it, but I see that Jan is not the only speedy Dutchman, to be quick off the mark, in this lounge. I'll be passing this information on to the Australian cricket team as a warning of hard times to come.

    Alan

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation list grouping? (IE 6.0)

    <hr>I'll be passing this information on to the Australian cricket team <hr>

    Now that's wicket ! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •