Forum Discussion
making a book list
HI, fairly new to excel and I am trying to make a book list containing book title, shelf location, author, and perhaps date published. It is for a bookcase with eight shelves and approx sixty books per shelf, the bookcase is quite full and I can't arrange them in order physically as they are of different sizes and that would use up too much space. Ideally the list would include some easy way of finding a shelf location from say book title or author, if a search box is only possible by writing code then this would be beyond me.
Have tried making a short sample list and the best I can figure out is to make the first column book title, then the adjacent column shelf location, then author etc, which means I can custom sort book title A-Z, which would make it easier to find, but what if I can only remember say authors name, how do I find a shelf location from this.
An alternative could be Power Query. In the attached file you can enter all books, authors, shelf locations and years in the large blue dynamic table. Then you can enter the search criteria in the small blue dynamic tables. You can search for one or more books, authors or shelf locations at the same time. After entering the search criteria you can click in any cell of the green table, right-click with the mouse and select refresh.
This is a variant on HansVogelaar 's solution.
BookSearchλ([title], [author], [shelf]) = LET( isTitle, ISBLANK(title) + ISNUMBER(SEARCH(title,BookList[Title])), isAuthor, ISBLANK(author) + ISNUMBER(SEARCH(author,BookList[Author])), isOnShelf, ISBLANK(shelf) + (BookList[Shelf]=shelf), FILTER(BookList, isTitle*isAuthor*isOnShelf) )- Thanks for the rely, power query sounds ideal, is there any "how to" for this
Here is another version which uses VBA to search the book's database on a sheet called "Search".
Replace the book database on the "Data" sheet with the actual data and then you may search the database on the sheet called "Search".
Take a look at the attached example. It uses the FILTER function (available in Excel in Microsoft 365 and Office 2021) to allow you to search by title, author of shelf.
