How to create a dynamic drop down list from multiple values?

You would make Named List List "Ponink" would be table with 001256,00126 etc... Then you need a named list that has all Company names.
Issue - no spaces in List names so you would need to truncate the company names

In the conditional for A2 - List - =Companies
In the conditional for B2 - List - =Indirect(A2)

Hi CRIMEDOG,

I think you didn't understand what I am looking for. The data in Sheet1 will change, so I want a dynamic drop down list on cell B2 of Sheet2 which takes the value in cell A2 of Sheet2 and then finds the corresponding Invoice No's in Sheet1 (if any) and create a drop down list of all the found items i.e. say I choose Royters Alif from the drop down list on cell A2 of Sheet2 then the items of the drop down list in cell B2 of Sheet2 would be 2000125, 2000126 & 2000139, and if I in future change the values (like add, delete or modify rows etc) in Sheet1, the drop down list on cell B2 of Sheet2 will automatically adapt to that and update the drop down list values.

Basically the value in cell A2 of Sheet2 is the lookup value, the data in Sheet1 table is the table array and Invoice No is the actual lookup result that I want but in normal lookup you get only one result but here there could be multiple results and the drop down list will consist of those results dynamically.

I hope this made you understand the problem better.

I am assuming Excel 2010+ version.

Sheet3, B2, copied down:
=IFERROR(INDEX(Sheet1!B:B,AGGREGATE(15,6,ROW(Sheet1!$B$1:$B$100)/(Sheet1!$A$1:$A$100=Sheet2!$A$2),ROWS(B$2:B2))),"")

to return the invoices. Then a Named range (called Invoices, CTRL-F3 to view/edit), to select the non-blank rows.

=Sheet3!$B$2:INDEX(Sheet3!$B$2:$B$100,SUMPRODUCT(--(LEN(Sheet3!$B$2:$B$100)>0)))

Then set the DV to List =Invoices.

is this what you wanted?

=IFERROR(INDEX(Sheet1!B:B,AGGREGATE(15,6,ROW(Sheet1!$B$1:$B$100)/(Sheet1!$A$1:$A$100=Sheet2!$A$2),ROWS(B$2:B2))),"")


Red: where this is true
cyan: return the row number,
Green: in ascending order
Blue: starting from 1 (it's a counter...)
Purple: finally returning the values from column B.

That formula would return (potentially) a list with the desired values and a long string of blank rows. The Named Range selects ONLY the non blanks:

=Sheet3!$B$2:INDEX(Sheet3!$B$2:$B$100,SUMPRODUCT(--(LEN(Sheet3!$B$2:$B$100)>0)))

Red: Starting from here
Green: Look down column B
Cyan: Until you find cells which no longer have a value with a length >0 (i.e. non-blank)

that then passes the non-blank cells into the DV box.

If it works for Excel 2010 it will work for later versions. It will not work (without modification) for earlier versions (your profile states Excel 2007, amongst others).



You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

Hi Glenn,

Can you explain why you chose 15 &6 in the first AGGREGATE function?
-------------
0Cool

In AGGREGATE, 15 is the subfunction number for SMALL.. so it returns rows (defined in cyan) matching the criteria (the bit in red AFTER the /) in an array in ascending order... so lowest row number first. the counter (in blue) returns them in ascending order.

The 6 subfunction tells AGGREGATE to ignore errors.