⭐⭐Free sheet here; www.gotsheet.xyz/c/dynamic-search-bar-in-google-sheets And, I've made an updated video with a new, cleaner solution for the search bar. Check it out here: th-cam.com/video/et0iWdbc0ys/w-d-xo.html
Upon looking into this further this helped greatly, but if you were to convert your search box to lower case in your formula it would not matter about case sensitivity using &LOWER(J2)& in your formula: " =IF(ISBLANK(J2,"",QUERY(Transactions,"SELECT A,B,C,D,E WHERE LOWER(B) CONTAINS '"&LOWER(C1)&"''")) "
Hiii, thank you! I saw this in my workmate's sheet a few years ago and now that I need it, I was struggling on how to use it. I have a question, is it possible to have multiple criteria in the search bar and/or have different search bars to filter the query outcome. My use case: I have a data base for rental listings, with your help I can already use the search bar to filter out by condo name, I want to take it further and be able to filter it by number of bedrooms for example (and more). It's basically like using the filter-click on a table but a more user-friendly way. Thanks in advanced hope this is possible.
Unfortunately, Query cannot keep hyperlinks. But, FILTER() will. Also, IMPORTRANGE() and ARRAYFORMULA() keep hyperlinks as well in case they work for your use case better than a filter.
Is there a way to use the query function to search for things containing text anywhere in the cell or does this only work when the text is in order? ie if the value is "Jackson's Meat Deli" but you search "Jackson's Deli" can you have the value return?
Yes and no. Whatever you put in the search bar will be treated as a continuous string. So it's not going to find anything for Jackson's Deli because those two words aren't in order in the results. "Jackson's Meat" or "Meat Deli" would both work, though. Also, I've made an updated video with what I think is a better way to make a search bar altogether. It will still take the full string like this one, though. Here it is if you're interested: th-cam.com/video/et0iWdbc0ys/w-d-xo.html
@@EamonnCottrellThe tutorial is greatly appreciated. I was able to create my first search function in a sheet. I don't know much about sheets, so this was everything!
This is just the video I was looking for! Thanks I'm using the Query function but is there a way to search all columns that contain the word found in cell J2? Rather than just searching column B? I'm using this for my project database so I'd like to be able to search for either project name, client name, account type etc
You're welcome; and yes, you can search multiple columns by adding OR statements in the Query. Check out cell A6 in The Search Bar sheet of the linked demo Google Sheet. I'm searching both column B and D in this query, and this is what you'll do for your sheet. I believe you do have to list each column separately and connect with the OR statements. Here's the query from my demo sheet: QUERY(Transactions,"SELECT A,B,C,D,E WHERE LOWER(B) CONTAINS '"&A3&"' OR LOWER(D) CONTAINS '"&A3&"'"))
Hi, I would like to add question to this if possible. Say the thing I'm searching is in a merged cell, I would like the whole row to appear in the search result. It's not a uniform number of cells high, but there is a cell row as a gap in between each subject. What would you suggest?
Merged cells do cause problems a lot of times. Sheets will typically only look at the top left most cell in a merged cell range. So if you've got data merged from B2:B3, it will only recognize the value as being in B2 and will only return, in our case, the 2nd row's worth of data. If I'm misinterpreting your exact setup, feel free to share a copy of your sheet with my full name at gmail and I can take a look at it to see if there's an alternative way.
Awesome. the query version worked so much better than filter for my application. I have a spreadsheet that contains information about numerous vehicles (asset ID, location name, location number, location history, plus vehicle data of chassis, builder, useage catagory.. its huge over 1500 rows). I was able to get the search working for text data but other data ie numbers didnt work.
Awesome! Yeah query is super handy. I have recently found another modified way to more easily use the filter. If you’re interested, it’s here. But glad you’ve got it working regardless 👍 Google Sheets and Excel - A Better Dynamic Search Bar th-cam.com/video/et0iWdbc0ys/w-d-xo.html
@@EamonnCottrell Thank you I'll review that too. Im not sure how to add the extra column searches (values) from columns A, D, or L As part of the search result the formula row pulled the first row from the data set as well.?? Wierd I used the formula: =IF(ISBLANK(E2),"",QUERY(FR_Fleet,"SELECT * WHERE LOWER(G) CONTAINS '"&E2&"' OR LOWER(K) CONTAINS '"&E2&"'"))
You can do this, but it will make the query a little more complicated. Check out the new tab I just created here. You can search 1-3 terms separated by commas in this example. If you look at the Query statement you can see how I did it and how to extend it to a greater number of allowed terms. docs.google.com/spreadsheets/d/1RrHff1f4Zm9rFTw-N5uWqiYXMdyeMNqRGujNMt_CaSA/edit#gid=1839042250
Great video, Eamonn! Thank you for making it. The data I am searching is a list of equipment where the title has the linear measurement of the item as well. Is there a way that you know of to use your search bar function to show results where the words I search are not in the exact order they are listed in the data? Example: the title of the item is exactly "AR O-LEDGER LW 2.13M (7'0")" but I would like to search in the search bar something like "Ledger 7'". Thank you for any guidance you can offer!
The way I've got it setup now with Query will allow you to type in any string of letters that are in the item. So if you type in Ledger, it will pull up that item. If you type in 7 it will pull up that item because the digit 7 is in the title. But it will break down if you type Ledger 7 because it will look for that exact sequence (the word ledger followed by a space and the number 7). Check out the demo sheet: I've added your item to the data and have put ledger in as the search term: docs.google.com/spreadsheets/d/1RrHff1f4Zm9rFTw-N5uWqiYXMdyeMNqRGujNMt_CaSA/edit#gid=1030063740 If you need more exact functionality, reach out and I can take a look and maybe come up with something a little more detailed for your sheet. My full name at gmail is where you can reach me. Thanks!
thank you so much for this! Is there any way to copy the format of my range as well? my texts have hyperlinks and different colored cells. I would like for it to copy the text along with the hyperlink
Oooo really nice question. So far as I know, there's not a way to do this with native formulas. We'd have to get into some Apps Script fun stuff which I believe would involve the getRichTextValues() and setRichTextValues() methods. I have not used these yet, but you've given me some ideas to try out for future videos...
What if you have multiple tabs and want to search and a result from any other tab? Example: FINANCE, INVENTORY, VENDORS. Could you provide the Query method ? thank you.
You can use the same technique where you query a named range. Make named ranges for the data on each of those new tabs. Make sure the ranges are of the same size i.e. (A1:D450) on each of the tabs. Even if it extends them to blank cells. Then in the first argument for the Query, you put each range separated by semi-colons and within curly braces like this...And I did notice that using LOWER didn't work when I used the column letter (LOWER(A)), but it did work when I used Col1 notation... =QUERY({finance_data;vendor_data;inventory_data},"select * where Lower(Col1) CONTAINS '"&B1&"'"). Here is a very rough proof of concept you can copy to start from and change the ranges etc: docs.google.com/spreadsheets/d/1tEwvnPl58l8rr54kfMb7Uivqdmu94smtyl3JPYUoS2Y/copy
I really like the idea of this search box and been trying to add this to my Sheet, some columns in my sheet are drop down selection columns and its only showing DATA from ABCD and (E) is drop down and wont show anything else past column D, anyway to use this still or this dont work with drop down columns. Thank you!
Yes, take a look at the query formula in A6 of the demo sheet. You can add columns to search through and/or words to search...here's what the condition part of it looks like right now: 👉👉WHERE LOWER(B) CONTAINS '"&A3&"' OR LOWER(D) CONTAINS '"&A3&"'"👈👈 The LOWER(B) and LOWER(D) parts are searching both column B and D for the item in A3. You could change one of the A3's to another cell if you wanted to have two places to enter two search terms. You can use the same setup to add more than two if you needed.
Hey there! I get around it in this example by querying LOWER(B) or LOWER(A) so that it turns the queried range lowercase to match whatever lowercase search term that I'm searching for.
I have a question, what if for isblank you want to display all data? how would you do it? here is my current code =IF(ISBLANK(B1)," ", QUERY('Inventory quantity'!A4:N,"select * WHERE LOWER(B) CONTAINS '"&B1&"'",1)) -- BTW great video!
No, the view only permissions would not let them make any changes to the spreadsheet. Someone would need to be able to edit it to type in the search bar that we've made. You could instruct them to make their own copy to edit by selecting File-make a copy if you needed your master copy to remain view only but you wanted to let them utilize a copy.
Does anyone know if I have a bookings spreadsheet for a set of workers. Id like a search or filter function that shows where they work quickly. They can work at multiple locations. Anyone know the best method at all please?
Depends on the setup, but probably this approach would work for that. You'd reference the worker's name in the search column and return their full entry. Also, an Xlookup function may work just as well if you're searching for one unique worker at a time. Here is an alternate search method I did: th-cam.com/video/et0iWdbc0ys/w-d-xo.html Here is an Lookup video I did: th-cam.com/video/3TO80uky0Xg/w-d-xo.html
Yes indeed! You can wrap the query statement inside an importrange statement like this: =QUERY(IMPORTRANGE(B2,"Sheet1!A2:C7"),"select * WHERE Col2 >10")
You bet. I just cleaned it up and added it to the description. Here's the link: docs.google.com/spreadsheets/d/1RrHff1f4Zm9rFTw-N5uWqiYXMdyeMNqRGujNMt_CaSA/edit#gid=1951720139
Hi some im trying yo use this on a spreadsheet i have for shows i review. But it was returning A parse error for my box when i selected. Ive managed to fix this, although not sure how but it now is showing an error for my text_to_search boxes selected. Not sure if you can help but would be appreciated 😊
I used a named range, "Transactions", to reference "Finances!A3:F225" by going to Data - Named Ranges and creating one. Then, you can reference that range on the Finances tab by using "Transactions" in formulas instead of typing in the range manually every time.
You'll need to grab it separately. So you can reference it in another formula as long as you don't mess with the query range. Check out the sample sheet I made. I cleaned it up some more and added columns demonstrating how to do this. Formulas are in the yellow highlighted columns: docs.google.com/spreadsheets/d/1RrHff1f4Zm9rFTw-N5uWqiYXMdyeMNqRGujNMt_CaSA/edit#gid=1951720139
@@MK-jn9uu yes its the demo sheet but you should be able to select File-make a copy to make an editable copy to mess around with. Let me know if you have any troubles.
You can edit the original data. If you try to edit it from the results area it will throw an error. But if you edit the original data it will repopulate in the results area automatically.
I'm trying it and it won't work. I don't understand how the 'Transactions' part works in the formula: =IF(ISBLANK(J2)," ",FILTER(Transactions,SEARCH(J2,B3:B225))) I'm trying to customize this as how I need to use it and tried: =IF(ISBLANK(D2),"1",FILTER($C6,SEARCH(D3,C6:1000))) But I just get the error "FILTER range must be a single row or a single column". I tried the Query one too. Same problem. The range is not recognized. Please help.
That was not as good of a formula as what I highlighted in the video. Transactions is the named range I used in my spreadsheet for all the financial transactions. I'm then using the SEARCH function to look for the search term in a column. I recommend using the formula in H6 on The Search Bar main sheet: =IF(ISBLANK(A3),"",FILTER(Transactions,(Finances!B3:B225=A3)+(Finances!D3:D225=A3))). You may be getting the error because you left out "C" in the last bit: C6:C1000. You've just got 1000. Same deal for the Query. Go to The Search Bar tab in A6 where I've got the working formula for query written out: =IF(ISBLANK(A3),"",QUERY(Transactions,"SELECT A,B,C,D,E WHERE LOWER(B) CONTAINS '"&A3&"' OR LOWER(D) CONTAINS '"&A3&"'")) This should take you straight there: docs.google.com/spreadsheets/d/1RrHff1f4Zm9rFTw-N5uWqiYXMdyeMNqRGujNMt_CaSA/edit#gid=1030063740
I WAS ALSO WONDERING IF I CAN USE THIS QUERY IF THER MAIN DATABASE IS FROM ANOTHER WORKBOOK, SO THE ONLY CONTENT OF THE SEARCH BOX SHEET IS THE SEARCHBOX AND SOME LABELS
Yes, you can combine IMPORTRANGE with QUERY to accomplish this. There was one quirk in doing this, though. It wouldn't work properly when I referenced columns by A, B, C etc in the Query statement. Instead I had to use Col1, Col2, Col3 etc for the Query to work properly. Here's an example of what the formula would look like in the SEARCH SHEET: =QUERY(IMPORTRANGE(A1,"Sheet1!A1:B10"),"select * WHERE LOWER(Col1) CONTAINS '"&B10&"'") In this example, I have the url to the DATA sheet in A1 which is what I'm referencing in the IMPORTRANGE function. Then I've got the SEARCH term in B10 in the CONTAINS part of the QUERY.
Fantastic question! I've added a sheet in the sample spreadsheet that shows you three ways to do this. You can highlight the cell if it's blank, you can add a note that pops up when you hover over the cell, or you can add a little code to enter that placeholder text when the cell is blank. Check it out: docs.google.com/spreadsheets/d/1RrHff1f4Zm9rFTw-N5uWqiYXMdyeMNqRGujNMt_CaSA/edit#gid=995248569
@@rjelstyx4909Sure thing...and yes, to apply that placeholder to a specific cell, you can use a named range like I have in the example. The C18 cell I've named "placeholder". Then in the apps script I'm setting the variable searchBar equal to that named range. And then checking for whether it's blank. But you can use this logic to test any specific cell(s)
Trying this on a book collection sheet I'm currently playing around with. I've got 4 columns: Title, Author, Language and Read/Not read. These start respectively on A4 through D4. In cell A2 I'm trying to add the filter, so *=IF(ISBLANK(A2),"",QUERY(Title, "SELECT A, B WHERE LOWER(A) CONTAINS '"&A5&"'"))*, but it returns a parse error. Am I missing something obvious here?
You should have A2 at the end of the Query instead of A5, I believe. If it still gives you an error, I'm happy to take a look - share it with me (my full name at gmail). Hope this works, though! 👍👍
@@EamonnCottrell Hi there. Changed the A5 to A2, no luck unfortunately. Shared my sheet with you. No rush at all, but if you could take a look; perhaps I'm missing something obvious.. thanks in advance
@@EamonnCottrell Just a heads up. I came across an article which spoke of separating via comma's or semicolons. When I try the following formula with comma's it doesn't work. Changing it to semicolon's does work; maybe a location thing. I tried to do the same with your snippet so to speak; unfortunately it returned the same parse error.. ``` // created a so called named range for easier reference =QUERY(books;"select A,B";2) ```
@@JeffKlunder I've got it working for you on your sheet: search in A2 and Query in F3. The commas were the culprit. 💡💡I learned something new: depending on the locale of the Google Sheet, semicolons are used instead of commas in some locales. So because your sheet is setup (File - Settings - Locale) in Netherlands, the commas I used to separate functions was throwing the error.
⭐⭐Free sheet here; www.gotsheet.xyz/c/dynamic-search-bar-in-google-sheets
And, I've made an updated video with a new, cleaner solution for the search bar. Check it out here: th-cam.com/video/et0iWdbc0ys/w-d-xo.html
thank you!
Upon looking into this further this helped greatly, but if you were to convert your search box to lower case in your formula it would not matter about case sensitivity using &LOWER(J2)& in your formula: " =IF(ISBLANK(J2,"",QUERY(Transactions,"SELECT A,B,C,D,E WHERE LOWER(B) CONTAINS '"&LOWER(C1)&"''")) "
Eamonn this was amazing! So easy explained and understood. Thank you so much!
Hiii, thank you! I saw this in my workmate's sheet a few years ago and now that I need it, I was struggling on how to use it.
I have a question, is it possible to have multiple criteria in the search bar and/or have different search bars to filter the query outcome. My use case: I have a data base for rental listings, with your help I can already use the search bar to filter out by condo name, I want to take it further and be able to filter it by number of bedrooms for example (and more).
It's basically like using the filter-click on a table but a more user-friendly way.
Thanks in advanced hope this is possible.
Yes. Check this out. I did an impromptu live stream showing how to do one version of this: th-cam.com/users/live8eY7Pht0-XU?feature=share
This is great! Can you please show me how to use query and not lose hyperlinks in the process?
Unfortunately, Query cannot keep hyperlinks. But, FILTER() will. Also, IMPORTRANGE() and ARRAYFORMULA() keep hyperlinks as well in case they work for your use case better than a filter.
Thanks, Eamonn! You are the best!
Thanks so much! Happy I could help out! 😁
FYI I made an updated, cleaner formula for this in case it's helpful: th-cam.com/video/et0iWdbc0ys/w-d-xo.html
@@EamonnCottrell Awesome! Thank you so much, Eamonn!
Is there a way to use the query function to search for things containing text anywhere in the cell or does this only work when the text is in order?
ie if the value is "Jackson's Meat Deli" but you search "Jackson's Deli" can you have the value return?
Yes and no. Whatever you put in the search bar will be treated as a continuous string. So it's not going to find anything for Jackson's Deli because those two words aren't in order in the results. "Jackson's Meat" or "Meat Deli" would both work, though. Also, I've made an updated video with what I think is a better way to make a search bar altogether. It will still take the full string like this one, though. Here it is if you're interested: th-cam.com/video/et0iWdbc0ys/w-d-xo.html
Thank you very much for the explanation!....
If anyone else is getting stuck on how he has Transactions as a searchable area, I believe he's made the selected areas a named range.
Correct! I've named the range A3:F225 on the "Finances" tab as "Transactions" so it's more readable in the formula.
@@EamonnCottrellThe tutorial is greatly appreciated. I was able to create my first search function in a sheet. I don't know much about sheets, so this was everything!
@jwright4862 Working Google Sheets are the Best
This is just the video I was looking for! Thanks
I'm using the Query function but is there a way to search all columns that contain the word found in cell J2? Rather than just searching column B?
I'm using this for my project database so I'd like to be able to search for either project name, client name, account type etc
You're welcome; and yes, you can search multiple columns by adding OR statements in the Query. Check out cell A6 in The Search Bar sheet of the linked demo Google Sheet. I'm searching both column B and D in this query, and this is what you'll do for your sheet. I believe you do have to list each column separately and connect with the OR statements. Here's the query from my demo sheet: QUERY(Transactions,"SELECT A,B,C,D,E WHERE LOWER(B) CONTAINS '"&A3&"' OR LOWER(D) CONTAINS '"&A3&"'"))
Hi, I would like to add question to this if possible.
Say the thing I'm searching is in a merged cell, I would like the whole row to appear in the search result. It's not a uniform number of cells high, but there is a cell row as a gap in between each subject.
What would you suggest?
Merged cells do cause problems a lot of times. Sheets will typically only look at the top left most cell in a merged cell range. So if you've got data merged from B2:B3, it will only recognize the value as being in B2 and will only return, in our case, the 2nd row's worth of data. If I'm misinterpreting your exact setup, feel free to share a copy of your sheet with my full name at gmail and I can take a look at it to see if there's an alternative way.
this is really cool, thanks for the help
Thanks Nicolas; this was fun to figure out and build. Glad it was helpful for you!
I really needed this, its such a great help! ❤️
Glad to help!
Awesome. the query version worked so much better than filter for my application. I have a spreadsheet that contains information about numerous vehicles (asset ID, location name, location number, location history, plus vehicle data of chassis, builder, useage catagory.. its huge over 1500 rows). I was able to get the search working for text data but other data ie numbers didnt work.
Awesome! Yeah query is super handy. I have recently found another modified way to more easily use the filter. If you’re interested, it’s here. But glad you’ve got it working regardless 👍
Google Sheets and Excel - A Better Dynamic Search Bar
th-cam.com/video/et0iWdbc0ys/w-d-xo.html
@@EamonnCottrell Thank you I'll review that too. Im not sure how to add the extra column searches (values) from columns A, D, or L
As part of the search result the formula row pulled the first row from the data set as well.?? Wierd
I used the formula: =IF(ISBLANK(E2),"",QUERY(FR_Fleet,"SELECT * WHERE LOWER(G) CONTAINS '"&E2&"' OR LOWER(K) CONTAINS '"&E2&"'"))
@@coghilla It's probably something to do with the FR_Fleet range. Happy to take a look if you want to share a copy with me. My full name at gmail
Awesome tutorial!
Thanks! Glad you enjoyed!
This is great. In the search bar, can you have multiple searches? Like I want to search everything with home, amazon?
You can do this, but it will make the query a little more complicated. Check out the new tab I just created here. You can search 1-3 terms separated by commas in this example. If you look at the Query statement you can see how I did it and how to extend it to a greater number of allowed terms. docs.google.com/spreadsheets/d/1RrHff1f4Zm9rFTw-N5uWqiYXMdyeMNqRGujNMt_CaSA/edit#gid=1839042250
Great video, Eamonn! Thank you for making it.
The data I am searching is a list of equipment where the title has the linear measurement of the item as well. Is there a way that you know of to use your search bar function to show results where the words I search are not in the exact order they are listed in the data? Example: the title of the item is exactly "AR O-LEDGER LW 2.13M (7'0")" but I would like to search in the search bar something like "Ledger 7'". Thank you for any guidance you can offer!
The way I've got it setup now with Query will allow you to type in any string of letters that are in the item. So if you type in Ledger, it will pull up that item. If you type in 7 it will pull up that item because the digit 7 is in the title. But it will break down if you type Ledger 7 because it will look for that exact sequence (the word ledger followed by a space and the number 7). Check out the demo sheet: I've added your item to the data and have put ledger in as the search term: docs.google.com/spreadsheets/d/1RrHff1f4Zm9rFTw-N5uWqiYXMdyeMNqRGujNMt_CaSA/edit#gid=1030063740
If you need more exact functionality, reach out and I can take a look and maybe come up with something a little more detailed for your sheet. My full name at gmail is where you can reach me.
Thanks!
thank you so much for this! Is there any way to copy the format of my range as well? my texts have hyperlinks and different colored cells. I would like for it to copy the text along with the hyperlink
Oooo really nice question. So far as I know, there's not a way to do this with native formulas. We'd have to get into some Apps Script fun stuff which I believe would involve the getRichTextValues() and setRichTextValues() methods. I have not used these yet, but you've given me some ideas to try out for future videos...
Excellent
So useful! You're awesome!
Glad it helped! Thanks a bunch!
FYI I made an updated, cleaner formula for this in case it's helpful: th-cam.com/video/et0iWdbc0ys/w-d-xo.html
Wonderful!! Thanks for sharing
you earned a subscriber 👍
Happy to hear! Hope this was useful for you!
What if you have multiple tabs and want to search and a result from any other tab? Example: FINANCE, INVENTORY, VENDORS. Could you provide the Query method ?
thank you.
You can use the same technique where you query a named range. Make named ranges for the data on each of those new tabs. Make sure the ranges are of the same size i.e. (A1:D450) on each of the tabs. Even if it extends them to blank cells. Then in the first argument for the Query, you put each range separated by semi-colons and within curly braces like this...And I did notice that using LOWER didn't work when I used the column letter (LOWER(A)), but it did work when I used Col1 notation... =QUERY({finance_data;vendor_data;inventory_data},"select *
where Lower(Col1) CONTAINS '"&B1&"'").
Here is a very rough proof of concept you can copy to start from and change the ranges etc: docs.google.com/spreadsheets/d/1tEwvnPl58l8rr54kfMb7Uivqdmu94smtyl3JPYUoS2Y/copy
FYI if you still have Q's or problems and have a sheet you want me to look at, shoot me an email (my full name at gmail.
I really like the idea of this search box and been trying to add this to my Sheet, some columns in my sheet are drop down selection columns and its only showing DATA from ABCD and (E) is drop down and wont show anything else past column D, anyway to use this still or this dont work with drop down columns.
Thank you!
Interesting. Are you able to share an example sheet for me to checkout the details to see if I can figure a workaround? (my full name at gmail)
is it possible to add other criteria to query search? More than one column?
Yes, take a look at the query formula in A6 of the demo sheet. You can add columns to search through and/or words to search...here's what the condition part of it looks like right now: 👉👉WHERE LOWER(B) CONTAINS '"&A3&"' OR LOWER(D) CONTAINS '"&A3&"'"👈👈 The LOWER(B) and LOWER(D) parts are searching both column B and D for the item in A3. You could change one of the A3's to another cell if you wanted to have two places to enter two search terms. You can use the same setup to add more than two if you needed.
Hi Eamonn! How do you disable case sensitivity in the Query function? Thank you! :)
Hey there! I get around it in this example by querying LOWER(B) or LOWER(A) so that it turns the queried range lowercase to match whatever lowercase search term that I'm searching for.
Thanks!
You’re welcome! Thanks a bunch; glad it was helpful for you 👍
I have a question, what if for isblank you want to display all data? how would you do it? here is my current code =IF(ISBLANK(B1)," ", QUERY('Inventory quantity'!A4:N,"select * WHERE LOWER(B) CONTAINS '"&B1&"'",1)) -- BTW great video!
Nevermind a quick chat gpt fixed it! once again thanks for the amazing video!
@@JohnDavidWoodcockAwesome! Good ol, chat gpt, my trusty helper! 😀😀
If a user has View access only, can they still use this dynamic search bar?
No, the view only permissions would not let them make any changes to the spreadsheet. Someone would need to be able to edit it to type in the search bar that we've made. You could instruct them to make their own copy to edit by selecting File-make a copy if you needed your master copy to remain view only but you wanted to let them utilize a copy.
Does anyone know if I have a bookings spreadsheet for a set of workers. Id like a search or filter function that shows where they work quickly.
They can work at multiple locations.
Anyone know the best method at all please?
Depends on the setup, but probably this approach would work for that. You'd reference the worker's name in the search column and return their full entry. Also, an Xlookup function may work just as well if you're searching for one unique worker at a time.
Here is an alternate search method I did: th-cam.com/video/et0iWdbc0ys/w-d-xo.html
Here is an Lookup video I did: th-cam.com/video/3TO80uky0Xg/w-d-xo.html
Hello Sir, can you please make a video on the query and import range formula using the search box with a search button in different google sheets.
Good timing! I just released a short video on How To Query One Sheet From Another Sheet:
th-cam.com/video/O04IJeXQNw4/w-d-xo.html
@EamonnCottrell make a video where we can search from another sheet with a search button. Will you?
@@sehrishali3362 yes, here is that video: Google Sheets - How To Query One Sheet From Another Sheet
th-cam.com/video/O04IJeXQNw4/w-d-xo.html
Can you query from another sheet?
Yes indeed! You can wrap the query statement inside an importrange statement like this: =QUERY(IMPORTRANGE(B2,"Sheet1!A2:C7"),"select * WHERE Col2 >10")
Can you share the practice file, so we can see the formulas more clearly.
You bet. I just cleaned it up and added it to the description. Here's the link: docs.google.com/spreadsheets/d/1RrHff1f4Zm9rFTw-N5uWqiYXMdyeMNqRGujNMt_CaSA/edit#gid=1951720139
Hi some im trying yo use this on a spreadsheet i have for shows i review. But it was returning A parse error for my box when i selected. Ive managed to fix this, although not sure how but it now is showing an error for my text_to_search boxes selected. Not sure if you can help but would be appreciated 😊
I can take a look at it if you want to share it with me - my full name at gmail.
how did you get the transactions tab to reference the tab?
I used a named range, "Transactions", to reference "Finances!A3:F225" by going to Data - Named Ranges and creating one. Then, you can reference that range on the Finances tab by using "Transactions" in formulas instead of typing in the range manually every time.
How can we utilize the filtered data though? The query breaks if I try to edit the returned table
You'll need to grab it separately. So you can reference it in another formula as long as you don't mess with the query range. Check out the sample sheet I made. I cleaned it up some more and added columns demonstrating how to do this. Formulas are in the yellow highlighted columns: docs.google.com/spreadsheets/d/1RrHff1f4Zm9rFTw-N5uWqiYXMdyeMNqRGujNMt_CaSA/edit#gid=1951720139
I think the sheet is locked
@@MK-jn9uu yes its the demo sheet but you should be able to select File-make a copy to make an editable copy to mess around with. Let me know if you have any troubles.
@@EamonnCottrell I swear I can’t. It should be “view only,” so I can make a copy, but the link takes me directly to the restricted access page.
@@MK-jn9uu Oh snap! I had somehow not saved it as view only. So sorry. Fixed now!
can i edit the data after it pop up at the search area, is it possible?
You can edit the original data. If you try to edit it from the results area it will throw an error. But if you edit the original data it will repopulate in the results area automatically.
I'm trying it and it won't work. I don't understand how the 'Transactions' part works in the formula:
=IF(ISBLANK(J2)," ",FILTER(Transactions,SEARCH(J2,B3:B225)))
I'm trying to customize this as how I need to use it and tried:
=IF(ISBLANK(D2),"1",FILTER($C6,SEARCH(D3,C6:1000)))
But I just get the error "FILTER range must be a single row or a single column".
I tried the Query one too. Same problem. The range is not recognized. Please help.
That was not as good of a formula as what I highlighted in the video. Transactions is the named range I used in my spreadsheet for all the financial transactions. I'm then using the SEARCH function to look for the search term in a column.
I recommend using the formula in H6 on The Search Bar main sheet: =IF(ISBLANK(A3),"",FILTER(Transactions,(Finances!B3:B225=A3)+(Finances!D3:D225=A3))).
You may be getting the error because you left out "C" in the last bit: C6:C1000. You've just got 1000.
Same deal for the Query. Go to The Search Bar tab in A6 where I've got the working formula for query written out: =IF(ISBLANK(A3),"",QUERY(Transactions,"SELECT A,B,C,D,E WHERE LOWER(B) CONTAINS '"&A3&"' OR LOWER(D) CONTAINS '"&A3&"'"))
This should take you straight there: docs.google.com/spreadsheets/d/1RrHff1f4Zm9rFTw-N5uWqiYXMdyeMNqRGujNMt_CaSA/edit#gid=1030063740
I WAS ALSO WONDERING IF I CAN USE THIS QUERY IF THER MAIN DATABASE IS FROM ANOTHER WORKBOOK, SO THE ONLY CONTENT OF THE SEARCH BOX SHEET IS THE SEARCHBOX AND SOME LABELS
Yes, you can combine IMPORTRANGE with QUERY to accomplish this. There was one quirk in doing this, though. It wouldn't work properly when I referenced columns by A, B, C etc in the Query statement. Instead I had to use Col1, Col2, Col3 etc for the Query to work properly. Here's an example of what the formula would look like in the SEARCH SHEET: =QUERY(IMPORTRANGE(A1,"Sheet1!A1:B10"),"select * WHERE LOWER(Col1) CONTAINS '"&B10&"'")
In this example, I have the url to the DATA sheet in A1 which is what I'm referencing in the IMPORTRANGE function.
Then I've got the SEARCH term in B10 in the CONTAINS part of the QUERY.
Here is a video showing how: Google Sheets - How To Query One Sheet From Another Sheet
th-cam.com/video/O04IJeXQNw4/w-d-xo.html
Is there a way to put "enter your name here" in the search box that goes away when you type something?
Fantastic question! I've added a sheet in the sample spreadsheet that shows you three ways to do this. You can highlight the cell if it's blank, you can add a note that pops up when you hover over the cell, or you can add a little code to enter that placeholder text when the cell is blank. Check it out: docs.google.com/spreadsheets/d/1RrHff1f4Zm9rFTw-N5uWqiYXMdyeMNqRGujNMt_CaSA/edit#gid=995248569
@@EamonnCottrell thanks for the reply. another question, after saving the script apply that placeholder to a specific cell?
@@rjelstyx4909Sure thing...and yes, to apply that placeholder to a specific cell, you can use a named range like I have in the example. The C18 cell I've named "placeholder". Then in the apps script I'm setting the variable searchBar equal to that named range. And then checking for whether it's blank. But you can use this logic to test any specific cell(s)
Trying this on a book collection sheet I'm currently playing around with.
I've got 4 columns: Title, Author, Language and Read/Not read. These start respectively on A4 through D4. In cell A2 I'm trying to add the filter, so *=IF(ISBLANK(A2),"",QUERY(Title, "SELECT A, B WHERE LOWER(A) CONTAINS '"&A5&"'"))*, but it returns a parse error. Am I missing something obvious here?
You should have A2 at the end of the Query instead of A5, I believe. If it still gives you an error, I'm happy to take a look - share it with me (my full name at gmail). Hope this works, though! 👍👍
@@EamonnCottrell Hi there. Changed the A5 to A2, no luck unfortunately. Shared my sheet with you. No rush at all, but if you could take a look; perhaps I'm missing something obvious.. thanks in advance
@@EamonnCottrell Just a heads up. I came across an article which spoke of separating via comma's or semicolons. When I try the following formula with comma's it doesn't work. Changing it to semicolon's does work; maybe a location thing.
I tried to do the same with your snippet so to speak; unfortunately it returned the same parse error..
```
// created a so called named range for easier reference
=QUERY(books;"select A,B";2)
```
@@JeffKlunder I've got it working for you on your sheet: search in A2 and Query in F3. The commas were the culprit. 💡💡I learned something new: depending on the locale of the Google Sheet, semicolons are used instead of commas in some locales. So because your sheet is setup (File - Settings - Locale) in Netherlands, the commas I used to separate functions was throwing the error.
@@EamonnCottrell I see, works like a charm. Many thanks!