Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combobox listfillrange (Excel 2000)

    Is there any way of filtering a combobox list fill range on the value from another combobox? For example, in the first combobox I want to select a rep code and in the second combobox I want the list to display only customers for that rep code.

    Any help would be greatly appreciated.

  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: Combobox listfillrange (Excel 2000)

    There are many ways to do this.
    It can be done via VB, it can be done without VB using formulas and some intermediate calcs

    Do you have an example dataset?

    Steve

  3. #3
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox listfillrange (Excel 2000)

    Here's some sample data. I had in my mind that I would do it by VB to eliminate any manual intervention. The end users are not experienced Excel users.
    Attached Files Attached Files

  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: Combobox listfillrange (Excel 2000)

    I don't want you to think that I've forgotten, it has been hectic and I haven't had time to sit down and work something up for you.

    Some thoughts (though these DON'T answer your original question about comboboxes):
    You could use Data-filter - autofilter to filter "inplace" the ID to see the customers
    You could also use the the advanced filter (Data - filter - advanced filter) to get a list of ID vs Customer

    I am assuming you want to extract data from the table, by having the user select an ID, then a Customer using comboboxes. If your goals are different, let me know since it might change how I do it.

    Steve

  5. #5
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox listfillrange (Excel 2000)

    Steve

    Thanks for getting back to me. Here's what I want to achieve: I have a sales database from which I want to extract a summary of sales to a single customer. The customer list is around 5,000 records which is too long for a combo box. If the rep selected his code first, then he would only see his accounts in the customer list - a more manageable list.

    Hope this is clear,

    Graham

  6. #6
    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: Combobox listfillrange (Excel 2000)

    This should get you started
    Press the button.
    A form is called (and it loads the list with unique, sorted items from col [img]/forums/images/smilies/cool.gif[/img]
    Select an item from the combo
    The second combo is filled with unique sorted customer names for that Rep ID
    Once that is selected a Message appears with the Customer selected, the ID and what Row number (or numbers if it is NOT unique in the list)

    Everytime the button is pressed it will regenerate the list so it will always take into account any new data that is added to the list. It sorts the list so the data does NOT need to be.
    Steve
    Attached Files Attached Files

  7. #7
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox listfillrange (Excel 2000)

    Many thanks, Steve. That's exactly what I wanted to achieve.

    Cheers, Graham

Posting Permissions

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