Search code examples
google-sheetsdropdown

Filter dropdown results in Google Sheets?

I have a sheet where I want to have a dropdown for each row, where the dropdown items are populated from a range on another sheet, but limited to only the items that match the product name.

Here is an example: https://docs.google.com/spreadsheets/d/1ikh5mSflbsxp_vnhHzXp2X9rmzDKmaX6GEeRZ6KsdZ8/edit?usp=sharing

So, in this example, the "Document" dropdown for "Widget 3000" should show options for

Sales Report 2019
Sales Report 2020
Warranty Info
Press Release

and so on for the other products. Right now, the data validation is just showing all the documents for all products.

It's kinda like a dependent dropdown, but not really... Not sure how to approach it, any suggestions?

Thanks!

Solution

  • use this per row:

    =TRANSPOSE(FILTER(Documents!B:B, Documents!A:A=A2))

    enter image description here

    then set up data validation per each row:

    enter image description here

    and use this in C2:

    =ARRAYFORMULA(IFNA(VLOOKUP(A2:A&B2:B, 
 {Documents!A:A&Documents!B:B, Documents!C:C}, 2, 0)))

    enter image description here

    and then hide auxiliary columns:

    enter image description here

    enter image description here