Dropdown List to return a unique value

[u234l530]

Please help me with my workbook as Iam novice to Excel.

I have 2 sheets, "STOCKS" and "ORDERLIST" .....I want to automate in Allocation Column the email address of the client matching the criteria of Product_Name,Size and Gender. its should return the email address as unique value, is drop down list possible? as it will facilitate me to choose whom to send. whatever is possible please help me with this formulae.

Thanks

Files below:

Workbook One Drive

[#2 · Accepted solution]

@u234l530 

Hi,

I use TRANSPOSE + FILTER function to filter out client emails.

Then I put =$K2# in the data validation list for the drop down. so now you can choose which email to send.

(I attached the excel INV Mngmt_dropdownlist.xlsx here).

 

[#3]

@rachel HI Rachel, Thank you for the solution. the Column K formula stays the same right? i have over hundred clients do I pu down the fomula and it shall work same way?

Appreciate the help

[#4]

Hi,

You can put bellow in cell K2.
=IFERROR(TRANSPOSE(INDEX(FILTER(Table1,(Table1[Product_Name]=Table2[@[Product_Name]])*(Table1[Size]=Table2[@Size])*(Table1[Gender]=Table2[@Gender])),,MATCH(Table1[[#Headers],[Email]],Table1[#Headers],0))),"")

Then you can just pull down the formula. it shall work.

[#5]

@rachel Perfect,it is  working as I needed it.  Only one drawback if the Client cancels and I delete the clients email from the Client's Order List, the email stays in the drop down list on the allocation Column.

[#6]

@u234l530 

I just tried to delete a row in Client's Order List. The dropdown list refreshed.
I am using Office365 Desktop.

Maybe you can try to set your "Calculation Options" to "Automatic".

 

 

[#7]

Hi Rachel, Works now Awesome...Thank you once again....have a great day ahead!