Forum Discussion

Copper Contributor

Oct 27, 2023

Solved

Dropdown List to return a unique value

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

excel

Formulas and Functions

Macros and VBA

office 365

Office Scripts

rachel
Oct 29, 2023
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).

INV Mngmt_dropdownlist.xlsx17 KB

rachel

Iron Contributor

Oct 29, 2023

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

INV Mngmt_dropdownlist.xlsx17 KB

u234l530
Copper Contributor
Oct 30, 2023
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
- rachel
  Iron Contributor
  Oct 30, 2023
  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.
  - u234l530
    Copper Contributor
    Oct 30, 2023
    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.