Google Sheets and Excel - A Better Dynamic Search Bar
ฝัง
- เผยแพร่เมื่อ 8 มิ.ย. 2024
- 🚀Join my free tech newsletter: got-sheet.beehiiv.com/
This is the best way to create a fully functional, dynamic search bar in your spreadsheet. I used Google Sheets so I could make it more shareable with the most number of people, but everything here will work in Excel as well.
Here's the formula from the video: =if(ISBLANK(C3),"",SORT(filter(transactions,SEARCH(C3,INDIRECT(C2))),3,FALSE))
Here's the sheet used in the video: docs.google.com/spreadsheets/...
=============================
Items & Courses (affiliate links)
My Amazon page with item suggestions I can't live without: amzn.to/3uqSO7Q
► Best Home Coffee Maker, hands down: amzn.to/3rjdjSr
► Best Mechanical Keyboard for data analysts: amzn.to/3NUDiaq
► Best Wrist Pain Remedy: amzn.to/44Oq7il
► Best Standing Desk: amzn.to/3PV4oRo
Some data course suggestions:
► Advanced Data Analytics Certificate by Google: imp.i384100.net/21dWGa
► Cybersecurity by Google: imp.i384100.net/VmN6oM
►Need a powerful automation tool for Google Sheets? Try Coefficient to automatically import data and sync with your business systems: coefficient.io/?via=984f9f
=============================
⌚Chapter Time Markers:
00:00:00 - Setup
00:00:45 - Breakdown
00:01:24 - The Formula
00:01:34 - How it Works
00:01:50 - SEARCH
00:02:51 - FILTER
00:04:40 - SORT
00:05:21 - IF
=============================
#googlesheets #spreadsheets #dataanalytics #excel #productivity - วิทยาศาสตร์และเทคโนโลยี
Been looking all over TH-cam just to find a tutorial like this! Done! Subscribed!🙂
Fantastic! Glad to help!
Thank you soo much, you don't have idea of how you helped me
Great! Glad it was helpful!
Thank you so muhc! This is so helpful and clean
Glad it was helpful!
This is so helpful! Thank you so much!
You’re welcome!
@@EamonnCottrell hi sir. mine is not working
@@SeanJuen-jv7rd What's the issue with it? Could be a lot of things.
Great 👍
nice one sir
thanks! Hope it's helpful!
These are excellent tutorials. I have a question: How did you create the drop down menu for search columns to match the column names in your transaction named ranges? Also, it appears I need access to get the google sheet in this video. Can I get access by any chance?
Hi there, that drop down search column is a bit of over-engineering probably. I created named ranges (place and category) that were those two columns on the data tab. Then in the formula for the search results, I used the INDIRECT function pointed at that drop down cell. Indirect returns a cell reference (the column of our data to search) referred to by a string (the named range).
You can make a copy to edit by selecting File - make a copy.
Hope that helps! Thanks!
This is fantastic, thank you so much for this Eamonn! I've tried to integrate it into my product data sheet and while it appears to be working, there's some odd behaviour in the first row of results. The first column shows every piece of data at once in the first cell, the second column shows every piece of data in that respective reference column, etc. Strangely, this only affects the first row and the rows below act as intended. Any idea what might cause this?
That is odd. Merged cells don’t play nicely. Do you have any of those in your data? Happy to take a look if you want to share a copy with me. My full name at gmail
I had the same issue with my attempt
@@coghilla Any merged cells causing this? Happy to take a look if you want to share a copy with me - my full name at gmail
Thanks! Any way you could return only some colums instead of all of them with this formula? Say I only wanted Place and Amount?
Cool idea, and yes. It would look like this: =if(ISBLANK(C3),"",SORT(filter({place,amount},SEARCH(C3,INDIRECT(C2))),3,FALSE))
I added this in the sample sheet on the SEARCH tab. You just have to add an array as the first argument in the filter and then make sure each piece (in my case the named ranges, "place" and "amount" are of the same size.
docs.google.com/spreadsheets/d/1pYtVD5DlF7n8mUPgqEGYQoGx8jY6UskM59_o7lI8_4o/edit#gid=1019782817
Hi there, this is fantastic! I was wondering if there was a way to make the data that is searched all sheets in the Google Sheet itself. Is there some way to make the search bar pull information from every sheet without having to type in all of the names of the sheets?
I'd create a new sheet that aggregates all the data into one place. For example, ={Sheet2!A1:B3;Sheet1!A1:B3}. This pulls the data from Sheets 1 and 2 together in the order that they're listed in between the curly braces. Then when all the data is together you can use that single source to search through.
Great video, I have multiple sheets of data to draw from, is there a way to make it so it looks in all the tabs?
I'd create a new sheet that aggregates all the data into one place. For example, ={Sheet2!A1:B3;Sheet1!A1:B3}. This pulls the data from Sheets 1 and 2 together in the order that they're listed in between the curly braces. Then when all the data is together you can use that single source to search through.
@@EamonnCottrell That worked great! I appreciate you getting back to me so quickly
Hi! This is really helpful. But I need your help, do you have tutorial for multiple search range and search term?
can i share my file with you so you can check it out?
@@rizzaicaro5566 Yeah let me know what you've got in mind - share with my full name at gmail
@@EamonnCottrell Hi, i shared the file with you. :)
The tutorial is awesoeme!
Is there any video where we can create a google sheets with importrange and a search bar where a user can search his name and get all details displayed in columns against his name? while others cant view any details without entering any name visibile in sheet
Could you clarify a little more? Is the data on a separate Google Sheet and you want to combine this search functionality with IMPORTRANGE? For performance's sake, it would be better to import the whole range onto a sheet of the spreadsheet you'll use for the search functionality. That way ImportRange isn't running over and over every time you change the search term. Then you could hide that sheet if you didn't want it displayed. I have run into issues with IMPORTRANGE when I use it a lot.
@@EamonnCottrell got it true its hard that sometimes importrange doest works to I had an issue earlier with 12k entries it failed ,
My query is let say that we are creating a google sheet and we want users to search using a search box in sheet without being getting access to the data in sheet of any other users.EG there are 5k of entries with details like Name RefId City Approval Date
A user simply opens the sheet it might be an xls or google sheet and he wants to know status of his approval he enters his refid in search box created and the data against it is ONLY displayed in this way no other user can view the entire sheet details or the data just his/her approval status
THANK YOU SOO MUCH
i keep getting error saying "FILTER has mismatched range sizes. Expected row count: 243. column count: 1. Actual row count: 1, column count: 1." I don't really know why..
This happens to me all the time. It's because in the range you've selected for the search column isn't the same size as the range your filtering. Check out the ranges or named ranges that you're using for the filter and the search functions. If you need a hand, share your sheet with me and I can probably spot the problem - my full name at gmail.
Thanks!
Great video. Is there a way to get it to search based on position, for example if you put "K" in the search field it returns every word the has a "K" in it. How would I make it so it only searches for "K" being the first letter of the word? Does that make sense?
Ooo, great question. I may have to expand into another video...but yes, to do what you need, you can replace the SEARCH function with a REGEXMATCH function like this (and I made a new tab in the linked demo sheet for you to check out):
=IF(ISBLANK(C3),"",SORT(FILTER(transactions, REGEXMATCH(INDIRECT(C2), "(?i)^"&C3)),3,FALSE))
docs.google.com/spreadsheets/d/1pYtVD5DlF7n8mUPgqEGYQoGx8jY6UskM59_o7lI8_4o/copy
@@EamonnCottrell Youre awesome, thank you so much
Is there a way to do this but with a "growing list" of data. For example, I am importing data from a form via Zapier, so the dataset will have the same columns (headings) but rows will be added after each form is completed.
Yes, I would simply make sure to have the range of the data you are filtering and searching defined so it extends down a long way. So if you currently have data from A1:E1000, just define the range in the formula to like A1:E2000 to give you room for 1000 more rows of data. Let me know if this makes sense and works when you run the zap
Is there any way to retain the formatting of the results? I'm using this for a listing of videos with links, and it wipes out the links and any text formatting. Thanks!
I made a sample tab called url formatting to test this. In my sample, it did pull the hyperlinks initially. However, if they are removed from the search result cell, they seem to go away for future results in that cell. The solution would be to copy and paste the entire data set (or at least the hyperlink column) into the range where your search results are going to go. Then delete all the values. This will set the formatting correctly of those hyperlink cells, and when they are populated by the search results you should be good. docs.google.com/spreadsheets/d/1pYtVD5DlF7n8mUPgqEGYQoGx8jY6UskM59_o7lI8_4o/edit#gid=1016375007/copy
Not working for me. Getting mismatched range error. Is there more that I need to do with the search sheet like add a named range, sort, etc.?
I expect it is either because of the named ranges I'm using for transactions, category, and place or because one of your ranges in the filter is not the same size as the other. I'm referencing those by named range in the formula. You can either replace those named ranges with the actual ranges where your data live or make sure that you have created named ranges.
For filter to work, though, the ranges have to be the same size (same number of rows). This is where I've made a mistake many times. Double check that too.
Can it be edited after searching, then it will be saved at the main data? Also is it possible to have it all in one tab and do not need a seperate data in another tab.. ty!
No, the results you pull up cannot be edited to change the main data. You would need to copy the search result to a new range, edit it and then copy it into the main data. You could code a more elegant way to do that, but it's beyond the scope of what I was doing here.
For getting everything in one tab, I've suggested to others to pull all the tabs together into one "master data" sheet that you can then do your searches on. For instance if you have to ranges of data, you can have them stack on top of each other in a new range using the following syntax: ={Sheet1!A1:C80;Sheet1!G1:I80}
Note the semi-colon in between the ranges. If you use just a comma, the ranges will be beside one another instead of stacked on top.
Thanks for the video; you're an amazing person ❤. Is it possible to save any changes in the correct row? Here is my problem: When I create a search bar, it returns the following columns based on the client's name (Col A = client no, Col B = client name, Col C = invoice no, Col D = invoice date, Col E = item). There is also a column after the item, which is Col F = quantity, but I didn't link it with the search bar. My problem is that when I enter a name in the search bar, for example, "Mohammed," it correctly returns the values of the above columns. However, when I set the number in the quantity column and then clear the search bar, the value I set changes to another client name. Is there any way to solve this problem?
Great question, and what you'll need to do is copy the search results (along with any additional quantities you're adding next to them) to a new range. I would select Copy, Paste Values Only. What's happening is that the cells where the search results appear are simply being repopulated every time the search changes. They won't "stick" in place there, you'll have to move them elsewhere for them to stay put.
Let me know if I misinterpreted anything...
Thank you for this. Is there any way to make this editable on a Google Site for others to search? I've created the spreadsheet with the dynamic search as you explained. But I want to allow others to search that database (that spreadsheet) through my Google Site. When I load the sheet to sites, the search line isn't editable for people to type. They'd have to open the spreadsheet. I want it all to live within my google site. Does that make sense?
Oooo. That's a good question. I found that when inserting the Google Sheet to Google Sites, it will not allow editing even if you have permissions on the Google Sheet so anyone can edit it.
However, if you instead select Insert - Embed and then Embed code, you can paste in an embedable with the link to your sheet and it will be editable like you wanted. Just make sure that you adjust the permissions on the Sheet to allow for either anyone with the link to edit or just those people who would be logged in to their google account when accessing the site. Here's that code:
@@EamonnCottrell thanks for the quick reply! That worked! My question now is how to set the sheet up so that when it's edited through the site, it doesn't change the actual sheet for all. When I edit the sheet through my site - i.e., change the search - it changes it on the sheet for all. If more than one person is trying to search at the same time, that won't work. How can I have one person's search only be viewable to them? I'm including links to other docs within the search so people really just need to be able to search to find those other links - nothing needs to be saved within the search.
@@mollytener3230 Gotcha. So, no, unfortunately when embedding the sheet, it is the same as looking at and editing the actual sheet. There's not a way to use the sheet (that I'm aware of) like a unique database for each user. You'd need to convert the date into another form like an html table and then use a little javascript to do what you're asking. There may be another way, but I don't know how off the top of my head without making it more of a web app than a spreadsheet.
@@EamonnCottrell yeah, that's way more than I know how to do. This will work! Thanks!
Fantastic. But how to search only specific value because in this case all the matching or contains value is getting fetched. How to avoid that
If you replace the SEARCH function with EXACT function, this will handle it. I made a new tab in the sample sheet with this example for you: docs.google.com/spreadsheets/d/1pYtVD5DlF7n8mUPgqEGYQoGx8jY6UskM59_o7lI8_4o/copy
@@EamonnCottrell omg this is exactly what i was trying to figure out!
@@rizzaicaro5566 Haha! excellent! Glad that's what you were looking for!
followed it to the letter hell i even put my data into your data sheet and nothing always some stupid error saying rows and coloums dont match. not sure what im doing wrong here hell i even wrtoe the formula out myself thinking the copy paste was doing it and still null...
Strange. It's something very small that's fouling it up, I'm sure. If you want to share your sheet with me, I can take a look. My full name at gmail.
Hi! I was able to make it work with my project. However, I found a problem when searching for numeric data. For example, when looking for number 3, it brings up all the numbers of the list that have that number. For word-based data is really helpful, but makes it hard with numeric data. Is there a way I can fix this?
Hey Natalia, Are you able to share an example sheet with me where you're running into the problem. I'm trying to recreate, but not doing something right because I'm still getting the results I expect if I enter a number instead of just plaintext. my full name at gmail if you can share your sheet.
Thanks for the help! I decided to create a second search bar only for the numeric columns and changed "SEARCH" to "EXACT" in the formula as suggested. Thanks again for the help!😄
@@NataliaJohan Excellent! Happy to help!
I tried to use this formula but it kept telling me that filter has mismatches range sizes. Tried changing the references but nothing worked.
It's probably off by 1 somewhere. This is what usually happens to me when i get that error. Check out the named ranges list if you're using the same setup as in the video. Data - Named ranges from the menu will open up the sidebar. My three ranges are "category", "place" and "transactions" and they all go from row 3: row 224. If you keep hitting a block, I'm happy to take a look. You can share your sheet with me - my full name at gmail.
I'm so close but so far away from getting this. I really want this to work.
You can do it. Share your sheet with me if you're hitting a roadblock on something and I'll take a look. My full name at gmail
Tried putting formula in an empty cell and it returned data in cells below... I need help with this
Array result was not expanded because it will overwrite data in c5
@@SBPromoVidsThis means there's not enough empty cells below where you're putting the formula. I've run into this a lot. Either clear out the cells below it so the full results can display or put the formula in a new location (maybe a new sheet). That's why in my demo I had it on it's own separate sheet so nothing was underneath it.
why is this not working for me?
all i get is N/A
Did you make a copy of the sheet to try out? May have to do with the named ranges. I’m using those instead of explicit ranges so if they’re different it’ll throw that error
mine is not working. why?
@SeanJuen-jv7rd What's the issue with it? Could be a lot of things.
@@EamonnCottrell can i share with you my sheets?
@@EamonnCottrell i dunno. it wont search the items I want to be searched. i can share with you the sheets file if you want.
@@SeanJuen-jv7rd Yeah share it with me and let me take a look - my full name at gmail
@@EamonnCottrell shared it with you sir