Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    San Diego
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mutal Pick Lists

    Anyone know how to have two drop down lists in a two adjacent cells where based on what you pick in either of the two columns the other column will fill in automatically? For example, column A is Company ID and column B is Company Name. If I go to cell A2 and select from the drop down list an ID, that cell B2 will automatically fill in that companies name. Or switch it around and select the company name in cell B2, and the company ID fill in automatically in A2.

    I had this set up so that column A was a named list and column B was a vlookup of the named list to get the company name. Now my manager doesn't have all the ID's memorized, and would like to select the company name and have the ID populated for him. I know I can do this with VBA, but was hoping for an easier solution.

    Any ideas?

    Scott B

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    A couple of thoughts ...
    Which version of Excel is being used?
    To satisfy the Manager can you just reverse what you did so far? Have Col A a drop down list of Company Name and Col B a vlookup to get the Company ID?

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    San Diego
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    tfspry,
    No, not what he was looking for, he always wants the best of both worlds. Anyway, I came to the conclusion that there was no way to do this without creating a circular reference, so I'm going to have to code it into the Worksheet_Change event handler. An easy task, but I have a lot of code in there already for other sections of the sheet and was hoping for a different way of handling it. But after thinking about it on my commute home, decide this was the only way to do it. That's the problem with letting management see what can be done with VBA, they get hooked and start asking for the moon and stars. Sigh.

    No further replies are necessary for this topic. Thanks everyone for reading it.

    Scott B)

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    You may want to consider creating Using Data Validation to create cascading lists, also called dependent lists. Some interesting explanations are here
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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