Google Sheets - Build Your Own Search Box
ฝัง
- เผยแพร่เมื่อ 30 ก.ค. 2024
- Create a search box that takes user input and highlights the rows where the word or number is found in the data.
🕒 Timestamps:
0:00 Intro
0:45 SEARCH Function
2:30 Make the Input Box
2:58 Conditional Formatting
Learn more from Prolific Oaktree:
🌳 Next Video - Google Drive - Free vs. Paid Versions • Google Drive - Free vs...
🌳 Related Playlist - Google Apps | Full Tutorials - bit.ly/google-tutorials
#googleseets #spreadsheet #tutorial - วิทยาศาสตร์และเทคโนโลยี
![[#SHORTS CLIP] ครูอึ้ง กรรมการอึ้ง ฮ่าๆ l ซานิเบาได้เบา l One Playground](http://i.ytimg.com/vi/qeBTuMJs4t0/mqdefault.jpg)
![BOWKYLION - เจ้านายคะ (work-life imbalance) [Official MV]](http://i.ytimg.com/vi/6bFj8G2pOi4/mqdefault.jpg)
Is there any way to make data appear un-highlighted until the search function is used?
Following the example of the video you have to put and AND condition on your formula, just only checks if the search box "IS TEXT" it will be drop False when is empty... so it works!!! Here u have the form: =AND(SEARCH($F$3,$C2)>0,ISTEXT($F$3))
Is there a way to do all of this by creating a front end using Google forms? I'm trying to find a cheap, decent database that has a great front end but nothing compares to filemakre pro. I tried the form but it only lets me back in with a link or the spreadsheet itself which is quite infuriating because I'm not sending it to others, it's only for myself. Thanks
Thanks for this!! It's very clear and easy to follow!
You’re welcome. Glad it helped.
Thank you master!! Would you pls show me how to indicating just only row having data that we search, so how could i do that? Thank you!!
Hello !! That is perfect well done ! How can we change , when the SearchBox is empty not to highlight everyting ?
You can do this with and and statement so that it checks if F3 isn't blank too =AND(SEARCH($F$3,$C2)>0,$F$3"")
thanks for the guideline! Its very easy to understand and helpful
Glad to hear that!
Hi! Love the videos, they are super helpful!
I am currently having difficulty with texts moving from their spot whenever I write something above it on Google docs. I have currently began writing a novel and made a table of contents with bookmarks and links to go along with it (with the help of your videos!). I have placed this table of contents as the first page to the document, which will be regularly updated as I write new chapters and add each one to the table of contents. My problem is that because the TOC starts as the first page and the "first page" to chapter 1 starts on the next page, how do I prevent that first page of chapter 1 from moving down every time I add to the TOC? I've tried searching the internet, mostly people suggesting I do a page break, but every time I try using it, it just doesn't seem to work for me (I'm probably just doing it wrong).
Thanks in advance for the advice!
Thank you, I'm glad you have enjoyed my videos. You may want to try a Section Break. See how they can work with custom page numbering here: th-cam.com/video/fDqm49_bD6M/w-d-xo.html
Can I add this search box on my google site and it directly shows the result based on the conditional formatting?
Great content, but I have one question, there is any way to put some text placeholder like "type to search" in the search box?
Hey! I'm trying to make a search bar for a homemade book catalog. Every time I do this though only some bars gets highlighted, and when I search it gives me a book two bars above the actual thing I am searching for. Is there a way to fix this?
Hello. This is very helpful!! Can we also search for data from a range of cells ? ex. C2 to M1000
Hi sir. Thank you for the tutorial. Have one doubt. Can you plz tell how to search multiple text or number in the search box?
Hi Adam, Enjoyed the tutorial thanks. I'm not a coder or anything but I was trying to create a search box facility on my website (like google search) that using Google Sheets could link to an external website that will have over 400 pages. The way I want it to work is the person would type their name in the box and if it was on the Google sheet it would appear in the box and once they clicked on it it would take them to that particular web page. Just wondered if you were able to point me in the direction of where I can find a video where someone explains how to do this. Thanks for your help in advance.
Hmm, use Apps Script and build some webApp to recall the particular data from Google Sheets.
i cant lock highlight the collumns, can you explain me clearly sir?
Thank you for very helpful video!
But what should I do if I want to leave the cell not in highlight when search box is empty?
1. Create another conditional formatting rule and place it before the SEARCH() function's conditional formating.
You can drag and drop by using the 4 dots on the left of each conditional format rule.
2. Apply to the same range you are using in the SEARCH() function.
3. Custom formula is... "=ISBLANK($B$1)" .
Replace $B$1 with whichever cell your search box is located in.
4. Set formatting style to None or White.
@@CurranOC Thanks for that additional function and for continuing to support your tutorial even though it's several years old.
THANK YOU SO MUCH SIR REALLY THANKFUL FOR THE INFORMATION !
Welcome!
thank you. needed this.
Glad it was helpful!
Thanks a lot. It helped me out !!
Glad it helped!
Any way to add search option in google form?
Very helpful, thank you!
You're welcome!
THANKS!
please what is the formula to make it search in all of the columns not only in the name column?
For example, I want to search with names maybe date maybe amount
Thanks for the video. Very helpful. How would I need to change the formula if I wanted to search the text of multiple columns instead of just one like in this example?
Wait! I figured it out. In case you are wondering... =SEARCH($A$2,$B2&$C2&$E2)>0 My search box is in A2 and the fields it searches are column B, C, and E starting on row 2.
@@OurTayloredHome Legend, ty!
The forumla just highlights everything. Do we need to change the '0' to a letter value?
can we get a search box to find multiple names at the same time using same box ?
Thank you
You're welcome
This is great and I've got it to work. I must be missing something but when I go back to edit the conditional formula, it doesn't show. It just shows a blank one and no others. Is it hidden somehow? If I want to change it, how do I do it without retyping? Lastly, could I use this formula to search across multiple sheets if the spreadsheet has them? If so, how? Thanks!
Make sure you highlight the range that is getting the highlighting applied. If you do that first, the conditional formatting rule should show up.
This is brilliant, can we do something like this to find different tabs available in one google sheet?
I don’t want to get into searching the content in each tabs though
I've been searching for something similar for weeks. No luck!
Please let me know if you're able to find something along these lines.
thank you , you help me you great
Hi, This is perfect thank you, is there anyway to get rid of the extra highlighting when there isn't anything in the search box?
I have the same question. If the search box is empty, the entire search field is highlighted
Oh, and when I use this search method, the cursor drops to the cell below the search box. Is there a way to move the cursor back up to the search box so I don't have to move it back up manually each time?
Not that I know of
I was able to get this to work, but it only searches one column. Which is fine. I’m wanting a second box to search the second column, but using the same formula and altering it to match my parameters is not working. Suggestions??
Wow that's nice, Thank you Sir.
You are most welcome
This was super helpful, thank you!
You're so welcome!
I have a question! When you delete what's written on the search box, all of the range will still be highlighted right? Then when you do type something, it will just highlight what you've written. Is there a way that nothing will be highlighted until I type something in the search box? Like it will remain its original color.
That's a great question. I can't think of any way to do that off the top of my head.
Use REGEX and IF function to do that
=IF(ISBLANK($F$3)," ",SEARCH($F$3,$C2)>0)
@@titunbandhu7174 Cheers dude! Saved me so much time!
Following the example of the video you have to put and AND condition on your formula, just only checks if the search box "IS TEXT" it will be drop False when is empty... so it works!!! Here u have the form: =AND(SEARCH($F$3,$C2)>0,ISTEXT($F$3))
I followed your tutorial but why is it not working for my google sheets?
Great video, helped me create a search box that works so thanks! The only problem now is that I have a large sheet with thousands of entries and the search doesn't automatically scroll down to the result so I'm scrolling manually looking for the highlighted item. How can I make it scroll to the result automatically?
Maybe go back to using the control F key combination.
@@ProlificOaktree ah maybe that would be better in that case, shame there isn't a way to display the results as it's satisfying to make something work, but thanks for your help and reply!
search box with vlookup
@@mobi47 Do you know how to set that up?
@@2trailertrucker983 yes its easy email me
Very helpful
Glad you think so!
But, it does not filter the data with the same name. In a big raw data, we might need that. Please suggest something.
He can we make it possible, if i share this sheet with many people and i only want each person to see their pay only, based on the email they sign in maybe? Or how so others don't enter other employees name and see their pay
That doesn't sound like something that I would try to do in one spreadsheet.
@@ProlificOaktree Please do, looking forward for the video
is there a way to have a search box on one page, when you search it looks in page 2, but then displays the results on page one?
You can do this by using queries
Thanks for the video, works great! Is it possible to hide rows that do not match the search text?
I don’t think so
@@ProlificOaktree That's a bummer. Thanks for the reply though!
Yes with a GAS that creates a filter.
@@agoogleuser6937 Thanks. Any more details on how to go about that? What is a GAS?
It is not working it just moves up and down to different rows but does not find the correct one. Please help sir
Second view!!!! Thank you for the info teacher!!
Welcome!
👍.......but how to convert image table into Google sheets
Tnx
YW
How to remove the background color and just highlight the one that I search for?
I poked around for the same thing and found this to work: =IF ($B$1>$C$1,SEARCH($B$1,$A4)) It's sloppy and requires that C1 block be empty but it does the trick. Basically If your cell has a greater value than the empty cell next to it, do the search. So as the two fields when blank are equal, it won't do the search until you put a value in the field.
Hopefully someone will come along and improve this as I'm just learning myself and I'm sure there's a better way.
Following the example of the video you have to put and AND condition on your formula, just only checks if the search box "IS TEXT" it will be drop False when is empty... so it works!!! Here u have the form: =AND(SEARCH($F$3,$C2)>0,ISTEXT($F$3))
This is Click Bait this is NOT a search Box! If you have an Inventory of a Thousand items just "Highlighting" the item ain't gonna make it.
It just says the formula is invalid
Only isssue I have is how to highlight the box for results and not when the box is empty
First view and first Comment 👍
🎉
Hi.. Just to check.. can you help us set up..we give you the fields and requirements..we will be happy to pay you for the services
Thanks for asking, I appreciate the compliment. I don't do individual consulting as my focus is creating content.
Awesome but in my case the key cell (F3 in your example) will be empty most of the time. How do I not let it highlight everything when its in its empty state .
Here's my solution: Create 2 key cells. Key Cell #1 (F3) is for user input, Key Cell #2 (F4) is the one used by conditional formatting. In Key Cell #2 insert the formula (=IF(F3=0,"@",F3). This means if the input cell (Key cell #1) is empty, Key Cell #2 will be "@" and therefore not trigger the formatting. When the user types a search query into Key Cell #1, Key Cell #2 will mirror it and trigger conditional formatting as intended. (You can use any symbol you want, as long as it won't trigger formatting).
You can completely hide Key Cell #2 by changing the font to white
@@andya6021 This is the correct Answer to alot of people's questions regarding removing highlighting! Nice Idea.
Following the example of the video you have to put and AND condition on your formula, just only checks if the search box "IS TEXT" it will be drop False when is empty... so it works!!! Here u have the form: =AND(SEARCH($F$3,$C2)>0,ISTEXT($F$3))
@@andya6021 Thanks Andy! Believe it or not I've been trying to get rid of the highlight for like 45 min now. lol
the formula's is not valid, why?
THIS SEARCH BOX IS SEARCHING THE CONTACT NUMBER???
just press ctrl+f, and search
How can can you tell me plz
help please