Hi, I am creating a spreadsheet that pulls information into data validation list and I'm trying to get a second data validation list to pull subsequent data associated with the first.

My first data validation list is using offset(worksheet!C2,0,0,SUMPRODUCT(--(worksheet!C2:C1000<>""))) to pull authors from the array and makes sure I don't get duplicates.



My second data validation list I'm trying to use index match to work with the first and only show books that are associated with the author. I'm using a simple index match formula but unfortunately if I have multiple books with the same author it won't pull all the books for the list. It will only list the first book found. Does anyone know how to overcome this?

Thanks -

My advice is:
Have a sheet with Table where all authors are listed;
Have a sheet with Table where all books with their authors are listed (in case the book has several authors, this book will have a row for every author in this Table). Authors in this Table are selected using Data validation list based on authors Table;
Have a hidden sheet with a column for every author in authors Table, which displays from books table all books from given author in matching column (to get it work, you must have in books table a (hidden) calculated column, where a number of book for given author is calculated (1st book of this author, second book, etc.).

How complex it will go on your report sheet (where you select author(s) and author books) depends on you selecting there a single author and his/her books, or having a Table where you select different authors and their books.

In 1st case, you have a data validation list above selected books table, based on author's table, where you select an author. Based on selected author, a dynamic Named Range selects a column with books from column of hidden table, where books from this author are listed. How many rows are in this dynamic Named range is determined by max order number for this author in books Table. And this Named Range is used as source for books validation list in your report sheet.

In 2nd case, you'll have data validation lists for authors in column of your report table. And you have to create the dynamic Named Range (needed for books validation list) dynamic not only according the selected author, but on report Table row too. I.e. for every row in report table, the dynamic Named Range from books Table must be calculated depending on selected author in given row of report Table.

It is not easy to do, but it is doable!

Suggest you attach a workbook with the minimum in it to show what you're wanting to do, with realistic data. This will answer a lot of unknowns and save us guessing.
In addition (quite important), tell us what version of Excel you're using.