SOLVED

Dropdown List to return a unique value

Copper Contributor

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

6 Replies
best response confirmed by u234l530 (Copper Contributor)
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).

 

Screenshot 2023-10-29 at 4.29.36 PM.png

 

Screenshot 2023-10-29 at 4.28.22 PM.png

@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

 

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.

@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.

 

u234l530_0-1698659115580.png

 

 

 

@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".

 

Screenshot 2023-10-30 at 6.06.56 PM.png

 

Screenshot 2023-10-30 at 6.06.24 PM.png

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

Accepted Solutions
best response confirmed by u234l530 (Copper Contributor)
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).

 

Screenshot 2023-10-29 at 4.29.36 PM.png

 

Screenshot 2023-10-29 at 4.28.22 PM.png

View solution in original post