Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Pick Lists (Excel 2000)

    I am trying to create a series of pick lists within excel for data entry. I will have four columns with pick lists in them headed Category/Product Group/Sub Product Group/Material

    The options that appear in the pick list for each column need to be dictated by what was entered into the previous cell.

    For example, if I entered "jewellery" into the Category cell (from a pick list), the options in the pick list for product group would be "bangle, neckchain, pendant" etc. Whereas if I entered "Electrical" as a category the options would be "Communications Equipment, Computer Equipment" and so on.

    The product group that is selected will then dictate what options are available in the sub product group field, which in turn dictates the list of possible materials.

    I have about 10 categories which each have between 2 and 15 product groups within them. Each product group has between 2 and 15 sub product groups and then items within each sub product group may be made from a number of different materials. Clearly with the number of permutations here, IF functions are insufficient for the task.

    I know that the answer probably lies somewhere within VBA programming, possibly some sort of user defined function utilising select case. But I have no idea how to program in VBA. In addition whatever is done in VBA needs to be done in such a way that it can run from within the list selection dialgoue found in excel under DataValidationAllow: List (then the box that says Source: - i.e. where you would normally type a cell range).

    Can anyone help??

    Thanks

    Pete

  2. #2
    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: Conditional Pick Lists (Excel 2000)

    Welcome to the Lounge.
    A simple way without any VB (it uses data validation) is outlined Here.

    Post back if you need additional info, or if this doesn't meet all your requirements.

    Steve

  3. #3
    New Lounger
    Join Date
    Aug 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Pick Lists (Excel 2000)

    You sir, are a genius.

    Works perfectly, thanks very much. And yes, that is signifcantly easier than programming!

  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: Conditional Pick Lists (Excel 2000)

    Thank you for the compliment, but I can't take credit for the tutorial. I only pointed the direction...

    Steve

Posting Permissions

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