Hello, what is the formula if i want to have multiple search boxes for advance search of an address sheet list. For example i want 6 search sections by name, business type, tag etc. that can be entered and show results of all data with the custom search criteria?
Thanks for this! I have checkboxes and dropdowns that are in my main list, however they just come up as "true" or, in the case of the dropdown, "false" unless something is selected in which case nothing shows up. Is there any way to make it view the checks and dropdown selections in the search sheet?
@@watchnlearnit, to be clear, if I buy the tool and input checkboxes, dropdowns, etc, when I search for the columns and they appear they will not only show the checkboxes and dropdowns, but let me affect them?
Hi, for example, if I search for 'apple in E2,' I want to see all the results that are not related to apple, that means it will show the orange, grapes, banana, lemon, tomato, papaya, asdf, or new added information, may i know how to do that , please?? Could you please also paste the video formula here for reference? thank you so much.
This was awesome thank you. My table has other google docs linked to them, but when I created my query the linked media didn’t transfer. Any suggestions?
@@watchnlearnit I was able to successfully follow your video. However, my sheets have data (google drive content) that was hyperlinked that did not transfer over. I was wondering if there's a way to solve this so all the linked files transfer over? Thanks for your help!
is there a way to make it search a non exact match? coz right now it has to be an exact match to get the result, if list has Apple, 'apple' or 'APPLE' or 'aPpLe' does not work, it has to be exactly like what it is in the list.
Hi, I am struggling with the ImportRange function as it takes a very long time to link. Can I do this without using an external URL and just link from another tab in the same sheet?
hi sir how to put these all search box on google site that user can search ?? because i made the search box but its not working user cant search for anything
How can I sort by text in a cell? I have a dropdown list in a cell that contains all the categories in column E. I just want to sort by that column by selecting a text-value from the dropdown list, categories after that one doesn't matter
Hi Ashok, I uploaded new video similar to this, searching from multiple sheets and not case sensitive. Check the link below. ruclips.net/video/JBqClPlBsUU/видео.html
I am not sure about that. You can search it in Google but I tried already for about 500k rows. You may check this one too. ruclips.net/video/iUoGLL5iURg/видео.html
Hello thanks for the video I am getting an error that says : "Query completed with an empty output". Any help please? Also, is it possible to do this on a single spreadsheet page rather than creating two separate spreadsheets?
@@watchnlearnit Thank you. There is something like this, for example, when you type jo in the word john, it searches, it forces you to type the whole word. Is there a solution for this?
@@UNKNWON-zg3ql yes I have can you share to me your sample spreadsheet to my email watchnlearnit@gmail.com Using contains in sql part you can achieve it but if you are still confused then better if you can share it to me so I can help you better
Another sheet for Item List: i.imgur.com/PNYjGcw.png Another sheet where you input search: i.imgur.com/5vpe7ZT.png Formula Explanation: i.imgur.com/AjWMmof.png =QUERY(IMPORTRANGE("1sc1tH2mFWM7go5ZW6SKGUekjniVzlR6qqqi1E5lXxZU","ITEM-LIST!A2:C11"),"SELECT * WHERE Col1 CONTAINS '"&E2&"' or Col2 CONTAINS '"&E2&"' ")
I uploaded new video similar to this, searching from multiple sheets and not case sensitive. Check the link below. ruclips.net/video/JBqClPlBsUU/видео.html
do you have tutorial about making a shopping cart style where you search your items then add them to cart and automatically totals the price? I was planning to make a spreadsheet like this thank you
Hi Brett, Good Day! You can use UPPER formula on your search box, even though you type lower case, it will read it as Uppercase. Here is the formula below =QUERY(IMPORTRANGE("","ITEM-LIST!A2:C11"),"SELECT * WHERE Col1 LIKE '"&UPPER(E2)&"' or Col2 LIKE '"&UPPER(E2)&"' ")
Hello Sir, I copied you the same as you did, but it's showing an error. Can you please text that formula in reply. Waiting for your response. Thank you
hello, pwede po ba yung hindi naka display yung data sa search bar unless itype? what if for masterlist with sensitive details like username and orders? thank you! :)
You can barely read the Code at the Top, this is really great idea, extremely Useful but if you can't teach us properly it only frustrates the hell out of people
Sorry about that Mark but I already made same new video with zoom in to clearly see the formula. Please check the link below: ruclips.net/video/JBqClPlBsUU/видео.html
First thing I noticed is you are moving WAY WAY WAY TOO FAST! You are not Providing enough explanation about what you are doing. I tried typing in this Code I didn't get a pop up asking me to Connect the sheets all I got was an Error. I hit enter and all my Code disappeared and nothing worked.
Sorry about that Mark but I already made same new video with zoom in to clearly see the formula. Please check the link below: ruclips.net/video/JBqClPlBsUU/видео.html
@@watchnlearnit I need to make this work, I have IMS System with Model number, Description, Quantity, and Location Code, I need to return Results for all of them
I uploaded new video similar to this, searching from multiple sheets and not case sensitive. Check the link below. ruclips.net/video/JBqClPlBsUU/видео.html
Hello, what is the formula if i want to have multiple search boxes for advance search of an address sheet list.
For example i want 6 search sections by name, business type, tag etc. that can be entered and show results of all data with the custom search criteria?
I think this is what you are looking at. Check the link below.
ruclips.net/video/iUoGLL5iURg/видео.html
Thanks for this!
I have checkboxes and dropdowns that are in my main list, however they just come up as "true" or, in the case of the dropdown, "false" unless something is selected in which case nothing shows up.
Is there any way to make it view the checks and dropdown selections in the search sheet?
I have a commercial version of this that I think it is feasible. Using Google Appscript this time.
ruclips.net/video/iUoGLL5iURg/видео.html
@@watchnlearnit, to be clear, if I buy the tool and input checkboxes, dropdowns, etc, when I search for the columns and they appear they will not only show the checkboxes and dropdowns, but let me affect them?
@@neiljohnson660 yes the whole row stay the same including your changes on the checkbox or dropdown
if you are interested with it. Just send an email to me watchnlearnit@gmail.com
@@watchnlearnit emailed
Hi, for example, if I search for 'apple in E2,' I want to see all the results that are not related to apple, that means it will show the orange, grapes, banana, lemon, tomato, papaya, asdf, or new added information, may i know how to do that , please??
Could you please also paste the video formula here for reference? thank you so much.
I will check on that if possible
Thank you so much. Im a Virtual Assistant and I just WoW'ed my boss
You're welcome. Glad I helped you.
thank you that was great would have taken me a long time to figure this out on my own
Glad it helped!
Please suppport my channel.
ko-fi.com/watchnlearnit
its a big help, thank you
Glad it helped!
This was awesome thank you. My table has other google docs linked to them, but when I created my query the linked media didn’t transfer. Any suggestions?
So far this only works between Google sheet. I'll try to make that same as yours.
@@watchnlearnit Hello, just wondering if you had any updates? Thank you.
@@luisramirezmartinez2503 what updates would you like to get?
@@watchnlearnit I was able to successfully follow your video. However, my sheets have data (google drive content) that was hyperlinked that did not transfer over. I was wondering if there's a way to solve this so all the linked files transfer over? Thanks for your help!
@@luisramirezmartinez2503 I will check that one and let you know for updates.
is there a way to make it search a non exact match?
coz right now it has to be an exact match to get the result, if list has Apple, 'apple' or 'APPLE' or 'aPpLe' does not work, it has to be exactly like what it is in the list.
yes that is possible. Share to me your spreadsheet.
watchnlearnit@gmail.com
nvm found your other vid about this, thanks@@watchnlearnit
@@watchnlearniti want know this also
Thank A lot! This Helped A spreadsheet i was making look really Professional!
Thank you for watching my videos. Please subscribe, like and share my videos :)
Nice tutorial. It would be helpful to query on words irrespective of letter case.
Thanks
Looks like I found the solution. ...."SELECT * Where LOWER(Col1) CONTAINS LOWER('"&E2&"') or LOWER(Col2) CONTAINS LOWER('"&E2&"') ")
@@LiamCrow great i have other videos also that related on how to make search box
May I know what's the background music you use in this video ? thanks a lot. It's very relaxing.
Awesome tutorial. The only problem is data is case sensitive. How to get rid off this problem
email me on my email watchnlearnit@gmail.com and i will send you a template
Hi, I am struggling with the ImportRange function as it takes a very long time to link. Can I do this without using an external URL and just link from another tab in the same sheet?
Yes, you can do this. I have another video for making a search box within same sheet. Check my other videos.
hi sir
how to put these all search box on google site that user can search ??
because i made the search box but its not working user cant search for anything
Check this one for better view of the formula.
ruclips.net/video/JBqClPlBsUU/видео.html
Let me know if this still not working on your end.
How can I sort by text in a cell?
I have a dropdown list in a cell that contains all the categories in column E.
I just want to sort by that column by selecting a text-value from the dropdown list, categories after that one doesn't matter
Email me your spreadsheet then and I'll check there
Can this be done in web excel 365?
Haven't tested it yet on MS Excel but I think it still same but the only difference is the link or IMPORTRANGE function.
Please
zoom in will help us to see, what you are doing.
Thank you
Hi Ashok,
I uploaded new video similar to this, searching from multiple sheets and not case sensitive. Check the link below.
ruclips.net/video/JBqClPlBsUU/видео.html
Is this way still able to handle data up to 10 million rows?
I am not sure about that. You can search it in Google but I tried already for about 500k rows.
You may check this one too.
ruclips.net/video/iUoGLL5iURg/видео.html
check this one from Google
support.google.com/docs/thread/181288162/whats-the-maximum-amount-of-rows-in-google-sheets?hl=en
How can I link multiple sheets from the same workbook?
Check out my other videos. I have made a tutorial for this.
This vid was very helpful. Can you tell me how to apply this to multiple columns?
Hi thanks for your appreciation. You may check this video link below.
ruclips.net/video/JBqClPlBsUU/видео.html
See the last part of video
Great , Thanks
You are welcome!. Keep watching my videos :)
Hello thanks for the video
I am getting an error that says : "Query completed with an empty output".
Any help please?
Also, is it possible to do this on a single spreadsheet page rather than creating two separate spreadsheets?
Yes possible. I will create a tutorial for single sheet. I will share with you once done.
try searching as it is even capitalize or lower case.
@@watchnlearnit Thank you I Will be expecting it . I really need it.
I've been given a project that requires that
@@watchnlearnit I got the error immediately I finished typing he code. I have not searched for any item at all.
Hello CodeHub,
If you can share your spreadsheet to me so I can check better and that would be great.
What code should I enter if I want to search non case sensitive text
Hello Sacay,
Please refer to this video link below for non-case sensitive search
ruclips.net/video/JBqClPlBsUU/видео.html
but how did you create the search bar?
I explained in the video using QUERY formula
how would i ignore case sensitive with this?
check this one ruclips.net/video/iUoGLL5iURg/видео.html
THANKS FOR THE GOOD INFORMATION BUT NO CASE SENSITIVITY HOW CAN WE DO
I made another for case sentisitivity, check other my other videos
@@watchnlearnit Thank you. There is something like this, for example, when you type jo in the word john, it searches, it forces you to type the whole word. Is there a solution for this?
@@UNKNWON-zg3ql yes I have can you share to me your sample spreadsheet to my email watchnlearnit@gmail.com
Using contains in sql part you can achieve it but if you are still confused then better if you can share it to me so I can help you better
@@watchnlearnit I can't add the 4th column I wonder how can I count
Hi Could you write the formula you used? Thanks
Another sheet for Item List:
i.imgur.com/PNYjGcw.png
Another sheet where you input search:
i.imgur.com/5vpe7ZT.png
Formula Explanation:
i.imgur.com/AjWMmof.png
=QUERY(IMPORTRANGE("1sc1tH2mFWM7go5ZW6SKGUekjniVzlR6qqqi1E5lXxZU","ITEM-LIST!A2:C11"),"SELECT * WHERE Col1 CONTAINS '"&E2&"' or Col2 CONTAINS '"&E2&"' ")
I uploaded new video similar to this, searching from multiple sheets and not case sensitive. Check the link below.
ruclips.net/video/JBqClPlBsUU/видео.html
do you have tutorial about making a shopping cart style where you search your items then add them to cart and automatically totals the price? I was planning to make a spreadsheet like this thank you
We'll try to that and you may check this one for better version of this.
ruclips.net/video/JBqClPlBsUU/видео.html
for those of you who messed up, it should be written as Col1 not col1.
Cool. Thanks for that note.
How can you make this formula non case sensitive?
Hi Brett,
Good Day!
You can use UPPER formula on your search box, even though you type lower case, it will read it as Uppercase. Here is the formula below
=QUERY(IMPORTRANGE("","ITEM-LIST!A2:C11"),"SELECT * WHERE Col1 LIKE '"&UPPER(E2)&"' or Col2 LIKE '"&UPPER(E2)&"' ")
@@watchnlearnit Thank you
Hello Sir, I copied you the same as you did, but it's showing an error. Can you please text that formula in reply. Waiting for your response. Thank you
Can you share your spreadsheet to me and I will have a look
watchnlearnit@gmail.com
I shared it with you.
Thank you dear Sir, it's working but I want if I search cell empty don't show anything. All the sheet remains empty
@@sehrishali3362 sure let me modify it again
Check again now.
Please zoom more whenever you record next. Tough to follow what's happening.
I did already on this specific video. You may check the link below
ruclips.net/video/JBqClPlBsUU/видео.html
hello, pwede po ba yung hindi naka display yung data sa search bar unless itype? what if for masterlist with sensitive details like username and orders? thank you! :)
Hello RIKI SHOPPE
Check this one. It won't show any data unless you search match value from the list.
ruclips.net/video/JBqClPlBsUU/видео.html
Is there a way to create a search box for "Multiple Sheet Files" ?
I think its possible and I will try and share with you. Just keep on checking and watching. Thank you :)
@@watchnlearnit By "Sheet Files", I meant Multiple Workbooks hehe
Hi Nexus,
Check this new uploaded video if this what you're looking.
ruclips.net/video/JBqClPlBsUU/видео.html
You can barely read the Code at the Top, this is really great idea, extremely Useful but if you can't teach us properly it only frustrates the hell out of people
Sorry about that Mark but I already made same new video with zoom in to clearly see the formula. Please check the link below:
ruclips.net/video/JBqClPlBsUU/видео.html
Can you please copy paste the entire code please 🙂
Hi Islam,
Try checking this video for better view and explanation:
ruclips.net/video/JBqClPlBsUU/видео.html
You can do this with a single sheet by using a query
Cool, can you share it to us?
First thing I noticed is you are moving WAY WAY WAY TOO FAST! You are not Providing enough explanation about what you are doing. I tried typing in this Code I didn't get a pop up asking me to Connect the sheets all I got was an Error. I hit enter and all my Code disappeared and nothing worked.
Sorry about that Mark but I already made same new video with zoom in to clearly see the formula. Please check the link below:
ruclips.net/video/JBqClPlBsUU/видео.html
If you can't make work yours. I can help you input the formula by sharing your spreadsheet.
@@watchnlearnit I need to make this work, I have IMS System with Model number, Description, Quantity, and Location Code, I need to return Results for all of them
Okay, if you can't make it just share publicly your spreadsheet with a dummy datas so I can work with it.
NOT WORKING
Hi, please share you spreadsheet to me watchnlearnit@gmail.com and i will check
@@watchnlearnit Email sent. Please check. I am stuck at this function for hours now.
I cannot understand properly because of
No explanatiin
I uploaded new video similar to this, searching from multiple sheets and not case sensitive. Check the link below.
ruclips.net/video/JBqClPlBsUU/видео.html
Sorry but can't read the formula :(
I've created seperate video for this and have a better view. Refer to this link below:
ruclips.net/video/JBqClPlBsUU/видео.html
Thank you! Very helpful videos!
press ctrl+f, and search, so simple
Dami mo naman vedio,mahigit isa🤣
haha nag start palang kasi paps :D
What's up with the country music? Are you making a search bar or milking cows?
Maybe both haha
Great tutorial!
Thank you for watching! :)