Learn how to use QUERY function with drop down validation lists to get dynamically filterer results of of a data-set in Google Sheets. #googlesheets #dropdown #tutorial
Пікірлер: 274
@nanditapandey1304 жыл бұрын
Thank you so much. You make videos in the simplest possible way where all of us can understand. I have started to enjoy working on Gsheets using your method of learning.
@agneserinkim4 жыл бұрын
thank you so much for breaking down this mysterious language of spread sheet filtering. you're really good at this!!!
@shawnieleaf22773 жыл бұрын
Dude your videos have been THE ONLY VIDEOS that have actually made sense, allowed me to do what I needed and actually get my project done. THANK YOU!!
@ExcelGoogleSheets3 жыл бұрын
Great to hear!
@marksawesomeadventures2 жыл бұрын
Thanks a lot, I was beating my brains out for like a Week trying to figure something out, some way to Search, I tried creating a Search Box, but nothing was working right, I simplified your ideas by just focusing on ONE Drop Down List, and no Sort, No ALL reps, just as basic as I could make it, and it worked! I finally was able to use this to Sort through an Inventory of 1500 items, and the Beauty of this method is it will actually list the item if it is located in two or three different places which helps to organize the Warehouse. And I was able to figure out how to use Google Sheets App to even look things up on my Cellphone. So you really helped me out a lot with this Video! Thanks again!
@kingfratetrain7634 Жыл бұрын
I was figuring this out for 3 hours and this vid finally got me to where i needed to go.
@williamland32103 жыл бұрын
Holy crap man your videos helped me so much tonight! I almost can't believe I learned as much as I did. Thank you!
@m3drob3 жыл бұрын
You deserve a like and commnet, it was almost exactly what i needed, i have to make a few adjustments, buti learned something new and more importantly solved my problem. Thank you.
@lucasmarquezmoratofedrigo99953 жыл бұрын
You are simply AMAZING! Thanks for sharing that!
@brook3518 Жыл бұрын
Loveeeee. You made my day. All the data were well filtered. It was very easy to get information of my Company much quicker!!
@beingRaigari9 ай бұрын
Amazing tutorial for a beginner to such stuff! Thanks mate. I loved it.
@willsgrandad4 жыл бұрын
Thanks for uploading, I have been looking at your Google sheets Query video's. I had worked out how to do a query from a drop down list, but yours is much tidier than mine. Keep up the good work.
@ExcelGoogleSheets4 жыл бұрын
Glad it was helpful!
@zubeirmsemo13403 жыл бұрын
Thanks!!! Really helped me gaining an internship
@giovani4514 жыл бұрын
Best g sheets professor on youtube. Pls continue doing the g sheets web systems with js videos
@adamcuda65433 жыл бұрын
Great video, helped a lot. I was looking just for this!
@vicentepbsaraiva2 жыл бұрын
Excellent tutorial. Very detailed and easy to replicate. Thank you.
@ExcelGoogleSheets2 жыл бұрын
Thank You!
@chrisber4 жыл бұрын
Your videos are bloody amazing, you should see some of the stuff I've made solely with lessons from your videos. Thank you
@vinodhrk3 жыл бұрын
Excellent video, it pretty much covers all my doubts..
@patriciaheizalnagginda71042 жыл бұрын
Thank you soooooo much for this tutorial. It just helped me finish my task!
@MinhTran-ix9ic2 ай бұрын
thanks man very much , your step-by-step instruction is super helpful
@MonjiGhanem3 жыл бұрын
Wonderful presenting!! Thank youvery much 👍
@danielbarker81862 жыл бұрын
THIS IS FANTASTIC! THANK YOU SOOOOOO MUCH!!!!!!!!
@HoldenMachining2 жыл бұрын
"1=1" that's what I needed. Well worth watching the video!
@davidgreen1051 Жыл бұрын
Very helpful - THANK YOU!!
@md.jinnah17542 жыл бұрын
Thank you so much for this tutorial.
@pichit.raetai4 жыл бұрын
amazing, thank you so much
@amjadhameed59324 жыл бұрын
Thanks so much, all your videos very useful
@rojijoshuabigay88492 жыл бұрын
Thank you very much, your explanation is very easy to understand
@nuristiazizah29327 ай бұрын
this one is really helpful. Thank you!
@Narikami9312 жыл бұрын
You're awesome! Thank you for the help!😁
@MaximeDUPONTmypage2 жыл бұрын
Thank you very much. You are doing a wonderfull job at explaining so clearly all these tips, tricks and how tos... bravo ! Looking at this video, may i ask : "how would you build the query formula if the dropdown menus are replaced by check boxes" ? Maybe an idea for another video subject... Thank you et, again, great job. I love your channel !!
@ExcelGoogleSheets2 жыл бұрын
:)
@JMPatel-nk8gr4 жыл бұрын
How do you know what i want next in google sheet,😍 U r reading my mind...
@scottrogers3782 жыл бұрын
Love your content man, you've taught me a lot
@ExcelGoogleSheets2 жыл бұрын
Great to hear!
@user-cp7lb3qk9q Жыл бұрын
This is really easy to follow and understand the process. Thank you very much for sharing
@ExcelGoogleSheets Жыл бұрын
👍
@vp23432a3 жыл бұрын
You're The Best!
@user-ec2kd8sz3t4 жыл бұрын
Awesome, thanks so much
@junaidsafdar91834 жыл бұрын
Wonderful Bro you solved all of my problems :)
@cameronblackmon95406 ай бұрын
Thank you so much!
@roweey3 жыл бұрын
Hi! You've been a great help learning advanced sheets functions! I was able to do this using multiple dropdowns, and importrange as well. I have a question, though. How do you hide all the data (source file) when the dropdown is blank?
@neilvanrooyen71963 жыл бұрын
"Whatever that little thing is"... It's basically a Table/Grid/Cells, ie, Your "Range" Selector. You're welcome. Great Video by the way.
@RajaGiri_tvpm2 жыл бұрын
Great help to learn 👍
@DanSaleem4 жыл бұрын
Thank you so much. Your channel is amazing. I have one problem when using Query function. I have a table that includes grade levels for a classroom, 1, 2, 3, and I have grade "K" for kindergarten. When I use query formula it does only show grades with numbers and omit the letter K for kindergarten. Is there a way to have the pull both text and numbers from the column using query?
@adminorigins6518 Жыл бұрын
very well explained step by step
@knarfseven6 ай бұрын
Amazing content. Thanks
@charlenenikadelcarmen82303 жыл бұрын
so nice! hope i can copy you in a just a single sitting haha
@teamisaacs3 жыл бұрын
When creating a query, is it possible to have the results include a hyperlink? For example, in my data set, I have hyperlinked items in each row that I am hoping will carryover when someone searches in the query. Great videos, BTW, thanks!
@scalemodelstudio2 жыл бұрын
Hi. I've been watching some of your tutorials. I've been wondering is it possible to add more criteria search box? like in your sample can I add "category" together with Region and Rep?
@Hafizh.Sandbox Жыл бұрын
Simply Wonderful
@luisdiez22494 жыл бұрын
Thank you so much for the tutorial! It is very helpful. I do have a question though. What if, after choosing the region and rep, I only wanted the customer, category and sales to pop up?
@ExcelGoogleSheets4 жыл бұрын
kzfaq.info/get/bejne/mL1mg5WK27Ldips.html
@tuershen44414 жыл бұрын
Thank you so much! I was able to use this filter for my work! Kudos!
@you3bi4b4 жыл бұрын
This is sooo helpful, thanks a lot! Question - Is it possible to carry over the formatting using the query function?
@ExcelGoogleSheets4 жыл бұрын
No, but you can format the fields. kzfaq.info/get/bejne/m7d7oKSUybzHk6c.html
@cmharliosne3 жыл бұрын
Great video. Got me about 98% through my project but what do I need to adjust in the final query if I have integers as my second drop down?
@hidayat68502 жыл бұрын
Thank you so much...
@No1TrendingToday2 жыл бұрын
Superb! thanks...
@sowsally Жыл бұрын
It's really help me to solve my problem. Thank you very much.
@ExcelGoogleSheets Жыл бұрын
👍
@sowsally Жыл бұрын
Can i make the data range as variable value?
@ExcelGoogleSheets Жыл бұрын
Yes. I have something like that in Dashboard series.
@dharmendrayadav-cn2fiАй бұрын
❤❤❤❤❤❤ It solved my two days problem
@kesifakademicom3 жыл бұрын
Thank you.
@KQueen39 Жыл бұрын
Thanks!
@ExcelGoogleSheets Жыл бұрын
Thank You!
@ash-16292 жыл бұрын
I wanted this tutorial with a single dropdown :( Good job tho, learnt a lot!👍
@michaelshaw76523 жыл бұрын
Nice trick using '1=1' to construct the query string
@rodney8325 Жыл бұрын
Thank you for this. Is there a way that we can select 2 or more regions or 2 or more reps, not ALL only?
@tareka4374 Жыл бұрын
Beautiful
@samwidwestlife4 жыл бұрын
Can we select only few particular cells(within a column) as a list of range for data validation?
@call2allcontent4563 жыл бұрын
This is super helpful, I am using the same equation and it is working, well. I have one adjustment I am trying to work on and would love your suggestion. I am trying to figure out how to filter videos that have multiple sets of data on the same line. Using your video example, if there were two names for "reps" (Jose, Olivia) and In the drop down menu I put "Oliva", the names for Olivia will come down except for the (jose, Olivia). Is there a way to adjust the formula to include that data. I am trying to create a formula that would use "contains" but A, not getting a working formula.
@mahonleddy358 Жыл бұрын
Did you find out how to do this? Having the same issue
@niaiseux4 жыл бұрын
Wow. That is very helpful, thank you! One thing I'm trying to figure out is how I can also add an ORDER BY XX statement to that Query. I can't see to get it to work.
@rayfrye13703 жыл бұрын
Juts add " order by (B) " where (B) is the column you want to order. Worked for me.
@erikabellido57363 жыл бұрын
Hey! Thanks so much for the videos! I have a question... I'm using the method you taught to do an aggregation function. For example, I want to change the name of the label, and I used the next function: =QUERY('Data Base'!$A$1:$J; "SELECT COUNT(I) WHERE 1=1 LABEL COUNT(I) 'Total'" &IF(A5="All Courses";"";" AND LOWER(I) = LOWER('"&A5&"') "); 0) When the filter is on "All courses" the function works good, but when I change the filter for another course, I have the following result: #VALUE... Do you know how I can solve this? Thank you a lot for reading. :)
@dieferman2 жыл бұрын
I just watch the video and I guess you already solved you problem, but in case someone else has the same problem, I will answer... "WHERE" is how we tell the "QUERY" how to filter the selected data. In your case the data should match "1=1" and what the "IF" statement returns at any given moment. "LABEL" is a completely different function that assigns a label to the column you indicate. Including the "LABEL" function in the middle of the parameters passed to the "WHERE" function is your problem. And, since the "IF" statement returns nothing when "All Courses" is selected, the "QUERY" works properly as it takes the "1=1" as the parameters for the "WHERE" and "LABEL" as another extra function. Solution, put the "LABEL" function after the "IF" statement: =QUERY('Data Base'!$A$1:$J; "SELECT COUNT(I) WHERE 1=1" & IF(A5="All Courses";"";" AND LOWER(I) = LOWER('" & A5 & "'") LABEL COUNT(I) 'Total' "); 0)
@devilyim2 жыл бұрын
Thank you for the video. This is my new experience to using this tool. Is there a way to implement this syntax to sort by date ?
@cmartin9084 жыл бұрын
Is it possible to have a two column dropdown list so that one value is displayed, with perhaps code in a second column that I want to drop in somewhere and use?
@simchi22384 жыл бұрын
Hi. I want to make 2 filters instead of just one but the 2nd filter would have to be dynamic meaning it would only show inputs based on the 1st filter that I make from the data set. Can you advise what formula I need to use? Thanks.
@mahireraslan63394 жыл бұрын
Hello thank you for that valuable knowledge! Additionally, I have a question, if I want to add the Date Column to the query function how can be combined with the written formula?
@ExcelGoogleSheets4 жыл бұрын
kzfaq.info/get/bejne/ma6Pkt2WsNLTZ4U.html
@mahireraslan63394 жыл бұрын
@@ExcelGoogleSheets thank you so much!
@mustansirujjain2 жыл бұрын
very nice explanation
@ExcelGoogleSheets2 жыл бұрын
Thanks!
@luvjoychoker78074 жыл бұрын
The dropdown menu is data validated, so was the 'lower' argument still necessary?. Thank you for the great video. Very helpful.
@ulisses20193 жыл бұрын
So, was it necessary?
@rajendraprasaadyadav17488 ай бұрын
Hi, I admired
@svetlinsem2 жыл бұрын
Thank you for the great titorial. How to add a function to show me if I have blanks in region and reps?
@sherazvarmayes4 жыл бұрын
I need to select columns based on Today as the date. Can i embed TODAY() into the Query function
@theuploader18284 жыл бұрын
hard to follow the instructions. But I won't stop to learn it. I really need this on my job. Anyways thanks
@ExcelGoogleSheets4 жыл бұрын
I'd suggest to start with basic & intermediate series before you get to this type of stuff. kzfaq.info/love/8p19gUXJYTsUPEpusHgteQplaylists Google Sheets for Beginners playlist Google Sheets Intermediate Tutorials playlist
@lazalazarevic61924 жыл бұрын
I didn't know you can concatenate IF statements with a query. Thanks
@ExcelGoogleSheets4 жыл бұрын
It's text, anything that creates text can be used as select statement.
@lazalazarevic61924 жыл бұрын
@@ExcelGoogleSheets Thanks for teaching me that! Thumbs up!!!
@albertohenao51674 жыл бұрын
Hey, How can I filter the options on the tabs? I have selected i.e. midwest but I want to filter the option related to Midwest
@Trancer0062 жыл бұрын
I achieved the same results by simply deleting the dropdown options (with Del key on the keyboard), didnt need to go the whole =IF route lol
@jamescurran9573 жыл бұрын
Amazing videos, Thanks! Hoping for guidance - I'm using 2 Google Forms to get data for item "drop offs" & "pick ups" I'm trying to track current location of a given item. I'm currently using a query between the 2 worksheets but its not set up right I assume. I get the list of data for the item, yes, but have 2 separate query's for each worksheet, and can't figure out how to simply get the most recent based on date.
@ExcelGoogleSheets3 жыл бұрын
Try to sort by date and limit to 1.
@jamescurran9573 жыл бұрын
@@ExcelGoogleSheets Ok Thx, I'll look into that, this can be set to automatically sort Query results? One last thing please, my main issue, can I combine query results (or any other formula you think is better) to pull the results from both Worksheets in one Query/Data set to then sort?
@ExcelGoogleSheets3 жыл бұрын
Yes, it can be sorted & limited automatically. Check QUERY series for more info. Yes, you can combine results or you can combine and then query as well. Check ARRAY videos for more info.
@Jpsans983 жыл бұрын
Wow so complex and feeling challenged! Hehe
@savm12352 жыл бұрын
How would this work if you want the query to populate based on either drop down, ie you want a list where reps are Edward or regions are South? (Not just where both are true)
@princemensah9519 ай бұрын
Excellent tutorials as always Sir. Is there a way to replicate similar results in excel. Thank you
@shaggqshaggy78164 жыл бұрын
Could you help me with adding whole list when the search box is empty? before i enter some data into it?
@ehsanamini85013 жыл бұрын
Great tutorial, thanks. For more readability, I guess you could have also used TRUE instead of 1=1.
@AdithyaShreshti2 жыл бұрын
This is just brilliant, the possibilities are endless if we keep exploring Google Sheet. How can we do such an universal search when there is data from multiple tabs in the same workbook?
@NoFutureForG0uda2 жыл бұрын
I cant get my queries to work directly on things like {Sheet1!A, Sheet2!A}, but if i spend an unused column somewhere it's fine. So for instance, in Sheet3, cell B1 I put ={Sheet1!A1:A1000, Sheet2!A1:A1000}, then I make my query =QUERY(B, "SELECT * WHERE B IS NOT NULL", 0), for instance, would grab first 1000 cells of Sheet1, then first 1000 cells of Sheet2, put them in Sheet3 column B, then populate column A with those 2000 cells except blanks.
@NoFutureForG0uda2 жыл бұрын
tl;dr : import from all your different sheets by using brackets { ; } (might be , instead of ;), maybe filter with a query as above to remove blanks (for instance your lists are probably not the same length, this makes sure you dont have blanks), then do your query
@callumhewson85143 жыл бұрын
Can you please tell me how I can link the drop down lists so they are dependent on each other etc.. so then only the specific results from what is chosen appear in the drop down as well as the resulting query.
@Cryscho5463 жыл бұрын
I have a question regarding if you can have multiple columns after the AND part in the string. So using my spreadsheet as an example, data is from a Pokemon game I am playing, and some of the Pokemon have 2 abilities which need to be in a certain order. There's over 380 data points so organizing it would be a pain instead what I am looking to see if my F2 field (Ability 1, currently on Intimidate) can be used to search both my K and L column from the Pokemon sheet. Right now it won't return all Pokemon with Blaze because some have it as a second ability. I've tried changing it so my L field will be searched by F2 as well but it just blanked out. Also thinking might be best to just duplicate Pokemon with more than one ability and just have a new row for them but I feel as if there is a way to make it so that it will use both columns in its query. Screenshot puu.sh/HFkLp/56223c4417.png =QUERY(Pokemon!G1:L,"SELECT * WHERE 1=1"&if(C2="All Tiers","", "AND LOWER(H) = LOWER ('"&C2&"') ")&if(D2="All Types","", "AND LOWER(I) = LOWER ('"&D2&"') ")&if(E2="All Types","", "AND LOWER(J) = LOWER ('"&E2&"') ")&if(F2="All Abilities","", "AND LOWER(K) = LOWER ('"&F2&"') ")&if(G2="All Abilities","", "AND LOWER(L) = LOWER ('"&G2&"') ")&if(B2="All Pokemon","", "AND LOWER(G) = LOWER ('"&B2&"') "),1)
@junnychoi8030 Жыл бұрын
Good evening, Appreciated your sharing of knowledge. How can I pull data from "a range of tabs (could be over 100)" altogether (not multiple one name by one name). I have a workbook consists of many tabs (with different name but most of them start with an Alphabet I). And I want to query or pull data of a cell from all the tabs starting the name with alphabet I to compose a summary report, thanks in advance.
@johncipolla83352 жыл бұрын
I am trying to morph this in to a tv episode watch list so far so good. I want to be able to search for tv series and check on my progress that has a date on it
@leekspinner7 ай бұрын
🤯🤯🤯
@guyvandesande83473 жыл бұрын
is it possible, using query to extract the first word in a column e.g. you have in row 2 column D the word Art dress. I wan't to have this in a new tab in split in two colums, like 'Art' in column D and Dress in another column e.g. E. now i use the formula with left and find, but when adding a new reaction in Formreactions 1 tab my formula is not automatically copied to a new row in the other tab
@ojasray14 жыл бұрын
how can i create a “Reset” Button?
@ta2dwez3 жыл бұрын
I think this is what I need? however can I get data from multiple sheets?
@rawyabasheer56244 жыл бұрын
thanks But why does the Function repeat or collect the data in the same row
@Blackonutts2 жыл бұрын
are you able to do this formula if a countifs or sumifs function?
@thomaswissel20823 жыл бұрын
How does a person add multiple OR statements to give the option to query any one four different fields, like salesman name or ID# and product name or ID#?
@ash-16292 жыл бұрын
11:07 "If I do Isabella" Go for it bro
@bryceslater12009 ай бұрын
I know this is an old video - but what if we wanted to add an "order by *" as well as a limit? Maybe even a variable limit?
@safexfacilitators33443 жыл бұрын
Very nice explanation thanks Can we add start date and end date function to this sheet, if yes then please show me it how to add date function
@ExcelGoogleSheets3 жыл бұрын
Check my dashboard series. There is an example there.
@karencepe53654 жыл бұрын
Hi, do you tutorial using data validation but data is from another spreadsheet? Badly need yout help. Thank you😇