Google Sheet - Make your own Search Box (Data from Multiple Sheet)
HTML-код
- Опубликовано: 21 окт 2024
- Simple way to create a search box and filter the list/ data automatically in your google sheets.
**OTHER TUTORIALS FOR GOOGLE SHEETS**
Google Sheets - Search by Date Range
• Google Sheets - Make S...
Google Sheets - Auto Update Options
• Google Sheets - Auto U...
Google Sheets - How to Highlight Row Based on Cell Value (Very Easy! Must Watch)
• Google Sheets - How to...
Google Sheets Print Unique Values Except Blank Cells (Made it Easy)
• Google Sheets Print ...
#googlesheets
#googlespreasheets
#spreadsheets
#excel
#tutorial
#googlesheetstutorial
#searchbox
#userinput
#sheets
#query
#search
#formula
This is really helpful, but what if there are 3 or more google sheet?
Wonderful! Can I know if I want the information that appears below after typing 'APPLE' in the search bar to be non-APPLE information? I'll really appreciate if you could help to solve this problem. Thanks.
I will check on that if possible
very good want this formula now i am very glad to find it
Thanks for the appreciation.
Hi, is there a way to not show the data from the other sheet when there's no text in the searchbar?
Yes, it is possible can share your sheet to me so I can check and help you with the formula
Very helpful formula. What if I have search value with upper and lower case letter?
That case you should use UPPER formula. On the source data you should have another colum for that for UPPER.
On search form, you should put UPPER also in thr formula.
You can share you sheet to me you have got confused.. So I can edit there directly
Please more tutorials on query function. I learn a lot from you
Yes, sure, I will upload more videos for Query function.
Hello!!! Would this work for multiple sheets in one SpreedSheet?
Yes, I have another video for that. Check that one.
@@watchnlearnit which video would that be? thanks
hi! can u help me, pls? :( i have a home page tracker that would search on 4 different sheets but all in one worksheet. i tried following the formula u provided to others who commented but it only read the first sheet >< how do i make it search in all 4 sheets? can u provide the formula if you don't mind :(( thank you so much this is such a life saveeer!
This video explain the exact thing you need. If you can share you spreadsheet to my email and that would be great so I can easily understand what is the issue.
My email is watchnlearnit@gmail.com
i had an question! Is it possible to search result out image instead of words?
hmm i don't think that is feasible. It might need an integration with other Google service which is Google image search.
@@watchnlearnit noted with thanks!
I wanted to post an Update on my Progress, I WAS finally able to figure this out, I wanted to talk about the mistakes I was making in case anyone else had these Problems. The first mistake I was making was trying to write the Formula Inside the same Cell as the Search Box. This won't work. You have to write the Formula in the Cell you want the Data to start Displaying. The Second mistake I was making was Writing the Formula ABOVE the Search Box, that won't work either. I figured this out by first practicing making Drop Down Lists and the Formulas for that is almost the same, then I just simplified it and instead of using the Drop Down list I used a Search Box, and behold IT WORKED! :D
This is the Formula I used in a Cell Below the Header Row where I wanted the data to display;
=QUERY(MASTER1!A2:I, "SELECT * WHERE A = ('"&A2&"')")
Thanks for sharing..
What if I have 21 columns? is there any way to shorten the script? Thank you
I will have a look if I can still make it shorter or better.
I have input the formula but when i go to press enter the formula turns into text. What can I do?
Make sure your formula has = in front
If still an issue, kindly share you spreadsheet to me watchnlearnit@gmail.com
The tutorial was very helpful. Thank you.. But when I select the Col6 (which is the list of names) onwards, it will not display. Do you have any idea how to fix it?
Make sure that the name from the list is type as UPPERCASE.
I am narrowing down my Confusion on this, so according to this Example I have to use Two Separate inventory Sheets? And in your Example you added data to one Sheet and it was Searched, but does this automatically add the data to the 2nd Data Sheet? So to be Clear you are using a Total of 3 Sheet? One Master list, One Duplicate Data List, and One Search Sheet Correct? And again if this is True how does the 2nd Data sheet get updated when I add to the Master List?
On this video example we assume that we have 2 separate spreadsheet and 1 spreadsheet where you search the data from 2 spreadsheet item. When you added new list from the ITEM-LIST spreadsheet you have to adjust also the range but you can set infinite range so that even you add new entry it will read it automatically then the formula should be like this ITEM-LIST!A3:C
If you still got confuse, I can help you just share your spreadsheet and give me the link.
I'm having some troubles, I try to search data in two sheets from the same origin spreadsheeet, and it works for the first sheet, but shows nothing from the second sheet. Would you please help me?
Hello if you can share to me your Google Sheet I can better help you with your issue.
my email is watchnlearnit@gmail.com
What if in my IDs I have numbers and letters? For example: 324CB4172ba2. I try taking the letters and it works. Is there a solution to have both numbers and letters in the IDs?? Thanks
Yes possible can you share you spreadsheet to me watchnlearnit@gmail.com
Kinda sucks .. followed from your old video to this and still couldn't get it working .. im not sure what's wrong.. I've followed exactly
=QUERY(IMPORTRANGE("1k9x2d1yhKI7I8Jpb_0H6zz0dfyqD196s6-4_kcsBgYQ","ITEM-LIST!A2:B10"),"SELECT * WHERE Col1 LIKE ' "&UPPER(D1)&" ' OR WHERE Col2 LIKE ' "&UPPER(D1)" ' " )
and it still shows ERROR
share your spreadsheet to me watchnlearnit@gmail.com so i can better check it
Haveing issue when applying error #value (unable to parse string for function query parameter 2:no_column1)
Hi, please share you spreadsheet to me watchnlearnit@gmail.com and i will check
Query only works with first col but for rest of the column in does show any result
share your spreadsheet to my email watchnlearnit@gmail.com so I can help you better what is the issue
Please check your email for the fixed. I have made some edit on your sheet and added some automation
What if I have multiple sheets in 1 spreadsheet how do I declare them in the code?
Will create a sample and share it to you in a few hours for better understanding.
It is possible and no need for IMPORTRANGE as the data is within the same spreadsheet.
Here is the formula or code below:
Similar to this video but didn't use IMPORTRANGE, just a QUERY formula and IFERROR, if the search value didn't found on the 1st item list, it will search on the 2nd item list.
Hope you get it :)
Let me know for any question.
=IFERROR(QUERY('ITEM-LIST1'!A3:C11,"SELECT * WHERE A LIKE '"&UPPER(E2)&"' OR B LIKE '"&UPPER(E2)&"' "),QUERY('ITEM-LIST2'!A3:C11,"SELECT * WHERE A LIKE '"&UPPER(E2)&"' OR B LIKE '"&UPPER(E2)&"' "))
@@watchnlearnit THANK YOU SO MUCH! YOU'RE A LIFE SAVER!
@@watchnlearnit Is possible to add multiple forms and sheets? I'm getting this error message 'IFERROR only takes 2 arguments, but this is argument number 3'.
I'm facing problem can you please help me
Hello Muhammad,
May I know your error please?
If you can share to me your spreadsheet and that would be great for me to solve your issue quickly.
here is my email watchnlearnit@gmail.com
Ok
I have a spreadsheet that has multiple entries for the same destinaion. For instance, Column A can have 3 different answers. How can I get those to show up as well. Only 1 answer shows up now.
Can share your spreadsheet please so I can check better. I am confused with your question as it can search any values.
Also, I want this search to work on my website. It shows up but does not let the user input their search request. Can you help with that?? Thanks!
I don't think that is possible. Google Sheet allows only to view it on your website using / embed.
@@watchnlearnit Can you still figure out how to make the different options show up?
You mean you want to have a dropdown options as a search instead of user input?
Gracias por su EXCELENTE VÍDEO, aunque aquí en MÉXICO NO RECONOCE LA FUNCIÓN UPPER( ), PUES LA CAMBIA POR MAYUSC( ) pero NO PROCEDE! ¿QUE SE PUEDE HACER EN ESTE CASO?
if UPPER() function is not available on your country, you can try using LOWER() instead.
@@watchnlearnit I found the solution! just by changing the single quote with a pair of double quotes. Now I will only see how to solve the accent of the words. Greetings.
Cool. Nice job. Keep on watching my videos and do subscribe if you like them :)
Excellent Tutorial. It works with two seperate sheets what if I have to pick up data from 11 more sheets, would that be possible by adding another function ?
Hello Hasan,
Yes, that is possible. You have to use more IFERROR function to forward the search on other sheet until the search value is found.
@@watchnlearnit how to do this sir?
@@codingcoding1286 please share your spreadsheet to me I will have a look
Sir I hope to have this really trying to work it on 5 more different spreadsheet :)
@@watchnlearnit Yey thank you so much Sir.
Thank you for the wonderful video, very helpful.
Glad it was helpful!. Thanks for watching :)
Anyone have this as a template? I simple cant get it to work, and i realt ned this funktion for my workshop :)
Share your spreadsheet to my email watchnlearnit@gmail.com and a details what you want to achieve.
@@watchnlearnithi can i share mine to you? i find your video really helpful but when i try it, it keeps failing
@@05.dianfajarariyanti61 Sure, share it to my email watchnlearnit@gmail.com and explain some details that you want to achieve.
i am finding it hard to apply the Query function to a date column. Purpose - to display all data that were entered on a particular date and then count the unique entries. can you help me how to get this done
Hi RSV Gaming,
Sure, I will create a new video combining the function QUERY + UNIQUE to achieve your request. I will upload and share it with you. Give a day to create this.
Hello RSV
You can try this one. I think this is what you're looking.
ruclips.net/video/oh9dFY8Pd3g/видео.html
@@watchnlearnit thanks a million for the response.
I'm wanting to make a search box on a front page that searches multiple sheets within a spreadsheet but when i apply the formula, it loads all the results from the first sheet where i have selected even when the search box is blank. Is there anyway i can set this up so the results boxes stay empty and only pull in the data from the sheets when searched?
Hi Gotty,
It did already what you want on this tutorial. If the SEARCH VALUE is empty then no result will show on the SEARCH LIST. If you can share your sheet to me so I can check this better that would great.
@@watchnlearnit Amazing thank you. I'm trying to have the table empty and only show results when searched but its always showing all the results from the first sheet and not allowing me to search for the second. Link is docs.google.com/spreadsheets/d/1f3EW2t1TRy_sQqJRjSEsrshHOKAld0VhSHJEQQNozgw/edit?usp=sharing
Give a day to do it. I will share it with you
when done.
Hi Gotty
Done now. See below link.
docs.google.com/spreadsheets/d/1d10CiN13SGpYs9rBfu-i66HJCcfCBZHJgzwFAWS9-BQ/edit?usp=sharing
Reason: Dont Merge the cells where the data and search value is located, the formula got confused where is the cell and what value to search.
Additional: - Added some formula to fix the case sensitive issue, using UPPER.
- Added IFERROR if no result it will show NO RESULT
Thats Alot!
ডাটা গুলো থাকছে না কে? সাচবক্স emty data emty?
I can't understand what you are trying to say. Say it in English please.
can you get me 2 file txt in this video? thanks
Sorry, didn't get that. What do you mean?
It’s not working for me :( only row 2 is showing even though I havent search anything yet. I tried your other tutorial where it’s not case sensitive and only 1 col search and that works for me. I want to able to search on 3 columns. Doesnt matter if it’s case sensitive or not.
This is what I tried =QUERY(IMPORTRANGE("XXX","Sheet2!A3:E"),"SELECT * WHERE Col2 LIKE '"&UPPER(G3)&"' OR Col3 LIKE '"&UPPER(G3)&"' OR Col4 LIKE '"&UPPER(G3)&"' ")
Have you tried already changing Col# to Column letter. Example if the First Name is located in Column A then the formula should be like this
WHERE A LIKE ......
Its not working on mine i dont know if what did i do wrong 🙃
Please share your spreadsheet so I can help you and see what is wrong.
@@watchnlearnit same here, error query completed with an empty output. I take the spreadsheet link anda sheet already like your tutorial
@@ahmadislami1041 Please double check your formula and if still not working then you can share your spreadsheet then that would be great for me to check properly to see what was the issue
It doesn't seem to work for me.. What am I doing wrong? Can you please help me out. Do you have an email/Whatsapp/Zoom/Slack or anywhere where I could contact you?
it would be better if you can share to me your sheet so i can check properly