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.

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

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.

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.

Make sure you mark the thread as solved