Unique dropdown list based on two criteria but...

[Darsk]

Hi Guys,

Having issues with an unresolved problem.

See attached file which has two tabs. First tab shows the table with 3 headers. Criteria 1 (Publisher) would be a dropdown list sourced from the info from the 2nd tab. Then Criteria 2 (Format) would give me another dropdown list based on Criteria 1, from the info on the 2nd tab. Criteria 3 would then give me another dropdown list based on Criteria 1 plus Criteria 2, from the info on the 2nd tab.

Now the catch is I'd like to have this available on each row.

I don't know VBA so was hoping there's a manual process. I've managed to find a solution if I only required one line, but I need up to 10 searches at a time.

And to really test your brains, could the drop list be autosorted and exclude any blanks (listing the descriptions only).

Hoping someone can bring joy to this workers frustrating week.

Darsk.

[JBeaucaire]

This is how I would do it. On the Product List sheet, I rearranged the main table, then sorted by Publisher, Format, then title to get them into a single list but grouped properly for one-table data validation. I created a named range called Descriptions out of column C. This range is dynamic, so it will expand/contract as you add/delete from your main list in column C.

Then I added a KEY column D to create an indexable range of titles that match the same category/subcategories easily. A dynamic named range called Key was created out of this column.

Then I used the Advanced Filter to put a list of unique publishers in column G. I created a Named Range called Publishers from this list.

This I used the Adv Filter on columns A:B to create a unique list of Publisher/Formats in I:J.

Next we switch back to Sheet1. I added the Data Validation to column A for Publishers.

Then we click on B2. With that cell selected, we create anamed formula called Formats, this uses the column I:J from Product List and the selected value in column A of Sheet1 to find the range of formats and display them. Once that is created, we apply data validation to column B using that new name.

Then we click on C2. With that cell selected, we create another named formula called Titles that pulls all the matching titles from the descriptions in ProductList column C, using the values selected on this row already for Publisher and Format and matching them to the added Key column. With that, we apply data validation to column C using that new name.

The result is attached.

You can add/subtract to the main listing in Product List, just keep it sorted when done.

Update the options in I:J as needed, also keep the sorting.

[Darsk]

THANK YOU! THANK YOU! THANK YOU!
This works perfectly. Much appreciated.