+ Reply to Thread
Results 1 to 6 of 6

VBA for creating dropdown list from dynamic multiple values lookup

  1. #1
    Registered User
    Join Date
    03-26-2013
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    18

    VBA for creating dropdown list from dynamic multiple values lookup

    Hi everybody,

    My questions are so difficult that it's hard to explain them. I will give it a try and please, don't hesitate to ask me further explanations if needed. :)

    I attached an excel where I have the following:

    - Sheet1: column A is for supplier, column B is for amount, column C should be the invoice
    - Sheet2: here I have the database. Column A for supplier, col B for amount and col C for invoice. For demonstration reasons, I concatenated column A and B into new column A. Now I have only col A (concatenated) and column B for invoice.

    VBA1: right click sheet1 and view code - this allows to create a custom drop down list from column A
    VBA2: simply called "blabla", it will lookup in sheet2 column A values in col B and arrange in col D. Please run it to understand better.

    Problem1: for VBA "blabla" I would like to do the same but without concatenate col A and B. To simplify ... eh, can't simplify it.
    Problem2: for VBA 1, the "MyList" drop down I would like to be created following the logic: if in sheet1, value from concatenate A&B is found in column D from Sheet2, then that row from sheet2 starting from column E = “MyList”.

    Final result: in Sheet1 C1 I should have in drop down list "inv1 ; inv4 ; inv5" , in C2 -> inv11 and in C3 -> inv12 ; inv13

    Well, that's it. Hope it's possible and not too hard.

    Thank you all,
    PS: of course that I'm a beginner, I found those VBA in other threads but couldn't modify the codes correctly.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: VBA for creating dropdown list from dynamic multiple values lookup

    I think i got it, let me know if it works
    Please Login or Register  to view this content.
    you could also throw in a line of code that sorts the data in column D-->x (where x = an unknown number of columns to the right of D) so it is in order from Supp1100->Supp2300
    Last edited by scott.s.fower; 04-26-2013 at 03:18 PM.

  3. #3
    Registered User
    Join Date
    03-26-2013
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: VBA for creating dropdown list from dynamic multiple values lookup

    Thank you Scott for your answer, it works perfect. Also,thank you for the added comments as I have this way the chance of understanding and learning something new

    Now, my project is half solved, the biggest issue being the dropdown VBA below or Problem2 from my first post. I can't figure it out how can this code be changed in order to show me in sheet1 col C all the invoices found in sheet2 after the VBA "blabla" has run. For any values inputted in col A&B from 1st sheet I should have in col C in dropdown list all the correspondent values for invoices found in sheet2 starting with col E. Is it possible?

    Final result: in Sheet1 C1 I should have in drop down list "inv1 ; inv4 ; inv5" , in C2 -> inv11 and in C3 -> inv12 ; inv13 and so on.

    Thank you!

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
    Dim lRow As Integer
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:A" & lRow).Name = "MyList"
    End Sub

  4. #4
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: VBA for creating dropdown list from dynamic multiple values lookup

    i added "Problem2" to blabla so it will do everything you want all in one go, which means that you don't need code on sheet 1 (on value change)

    here's the workbook (took out the sheet 1 code, and updated blabla to the code below)
    questionForum.xlsm

    here's the code
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-26-2013
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: VBA for creating dropdown list from dynamic multiple values lookup

    wow, it's solved ! Thank you so much Scott, it's exactly how I wanted to be.

    If this thread will ever be useful in the future to others, here it is what this code does: for lookup with multiple variables and multiple returns, it show's you in a dropdown list all the values found in your database.

    Thank you once again, it's very useful for my project.

  6. #6
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597
    Make sure you mark the thread as solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1