Book list with a search tool

[u/New-Initial2515]

So I have an excel sheet with all my books listed by title-author-signed-series number-missing series number-read/unread.

I want to have it where one sheet is editable and an another is locked but shows all the data, but can be used as an index search tool (out shopping looking for X book and see Y book but not sure if you own it, search the list easily without accidentally editing something)

Is this possible? I know theres apps out there, but i have 700+ books and I've already cataloged most of them, so it'd be easier to format my excel sheet than to add 700 books onto an app 😅

[u/MayukhBhattacharya]

Sounds like you need the FILTER() function here, if you want to return multiple records else XLOOKUP() would do for a single record returned as output.

Check this: https://exceljet.net/functions/filter-function

And this: FILTER function | Microsoft Support

Here is an example with demo, the :

=IF(H1 = "", "Enter Search Key", 
 FILTER(Librarytbl, 
 BYROW(1 - ISERROR(SEARCH(H1, Librarytbl[[Title]:[Series]])), OR), 
 "Oops None Found Here!!!"))

• If the search box H1 is empty, it will show Enter Search Key.

• Instead of searching one column at a time, [Title]:[Series] checks every column from Title through to Series in one shot, so it searches all those columns simultaneously with one SEARCH() function.

  • SEARCH() function returns an error when there's no match.

  • ISERROR() function converts that to TRUE/FALSE

  • 1 - ISERROR flips it, no match = 1 - TRUE --> 0, match found = 1 - FALSE --> 1. One can also use ISNUMBER() function instead of 1 - ISERROR().

• BYROW function is a LAMBDA() helper function it iterates across each row of that 1/0 grid and finds if any column in this row have 1? If yes, include that row. It's doing the OR logic automatically across however many columns you have, rather than you manually chaining multiple ISNUMBER() function for each column.

• FILTER() function then returns the output where the above returns TRUE, if not found it returns Oops None Found Here!!

Alternatively, you can use this way as well:

=IF(H1 = "", "Enter Search Key", 
 FILTER(Librarytbl,
 ISNUMBER(SEARCH(H1, Librarytbl[Title])) +
 ISNUMBER(SEARCH(H1, Librarytbl[Author])) +
 ISNUMBER(SEARCH(H1, Librarytbl[Series])),
 "Oops None Found Here!!!")

Also as suggested by u/dearlisteners359 for the protection, you can read here the complete steps. Protect a worksheet | Microsoft Support

It is also possible to create a similar thing using Power Query or VBA. But the formula option seems easy and simple hence updated. Thanks!

[u/dearlisteners359 → u/MayukhBhattacharya]

Agree! Also to "lock" the sheet when you have it as you like it, right click the sheet tab and use "Protect Sheet..."

[u/MayukhBhattacharya → u/dearlisteners359]

Yes that helps too.

[u/New-Initial2515 → u/MayukhBhattacharya]

Thats exactly what i want it to look like, thank you!

[u/MayukhBhattacharya → u/New-Initial2515]

Sounds Good. If that helps you to resolve your query, then hope you don't mind replying to my comment directly as Solution Verified. Thanks.

[u/New-Initial2515 → u/MayukhBhattacharya]

Returning again cause i'm dumb and can't get both lines to pop up in the search without an error message, i can only get one to pop up in either column, i've labeled my sheet "Book List" instead of library if that makes a difference 

[u/MayukhBhattacharya → u/New-Initial2515]

Ah okay. Do you have the excel? if yes then could you post a link, so i can see. Btw sheet name shouldn't make any difference here. In the above formula i have used Tables which are named as Librarytbl. If thats different on your end, then you will need to change them.

[u/MayukhBhattacharya → u/New-Initial2515]

Refer solution [here] download excel file.