Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Creating a user tool to match and combine lists (Excel 2003)

    Aloha all,
    I have a project for which I can sure use the advice of the Excel genii (read geniuses plural) that frequent this forum. I really don't mean for someone else to do my work. I'm just hoping for some advice, and maybe a finger pointing me in the right direction to resources I can use.

    I have to make a tool for agents to match up their sales transactions with a name from their client list. An ideal process would be:
    Step 1. agent selects a transaction (or agent sees which transaction to work on),
    Step 2. a list of clients appears and agent finds and selects the appropriate client,
    Step 3. information from the client list is added to the transaction list.

    The poser for me is Step 2, how to present the client list to the agent so agent can make the match. The client list is usually at least 100 rows and can be as much as 700 rows. The transaction list might be typically 100 rows. Agents can have more than one transaction with the same client, so the full client list has to be available for each transaction. Both lists (the transaction list and the client list) can be in separate sheets in the same file.

    I've thought of 2 possible tacks:
    Tack 1. Use data validation in a 'client name' column in the transaction list to make an in-cell dropdown, and then when a name is chosen use event handlers to copy additional info from the respective client record to the transaction list.

    Data validation allowing a list requires any cell reference to be on the same sheet, so I have to either make a column of the client names somewhere on the transaction sheet, or using code create a string with the list of names from the client sheet and set the Formula1 property of the validation object. Can validation allowing a list accomodate a list of 700 names?

    Tack 2. create a combobox or listbox, maybe on a floating commandbar, for agents to pick a name to match with the current transaction.

    I started into creating a custom commandbar with a combobox control, but it got too confusing. I've been using Excel for 15 years and making my own macros since the macro sheet days. I'm new to Excel 2003. I'm adept enough at creating macros that deal with spreadsheet tasks, but I've never had to use a class module and don't know why it's necessary. The examples and help topics I've found so far in VBA help and Excel help are only confusing me here. MS does a wonderful job of obfuscation.

    Can anyone make any suggestions - is one of the two paths better? Easier? Is there a better way? Many mahalos for your feedback in any regard.

    John Jacobson

  2. #2
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Creating a user tool to match and combine lists (Excel 2003)

    Never mind folks. I see that I can use data validation. I can create a defined name that refers to another worksheet or workbook. I should be able to figure the rest out. Mahalo anyway

    John Jacobson

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a user tool to match and combine lists (Excel 2003)

    You make helping you too easy. Glad you figured it out.
    Legare Coleman

Posting Permissions

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