QUERY - Drop Down List to Filter Data - Google Sheets
Вставка
- Опубліковано 29 вер 2024
- 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
How do you know what i want next in google sheet,😍 U r reading my mind...
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.
Glad it was helpful!
Thank you so much for this tutorial.
Very helpful - THANK YOU!!
Nice trick using '1=1' to construct the query string
The dropdown menu is data validated, so was the 'lower' argument still necessary?. Thank you for the great video. Very helpful.
So, was it necessary?
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 ?
Thank you so much!!!
Thank you for the great titorial. How to add a function to show me if I have blanks in region and reps?
Thank you so much...
thank you so much for breaking down this mysterious language of spread sheet filtering. you're really good at this!!!
Excellent tutorials as always Sir. Is there a way to replicate similar results in excel. Thank you
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?
are you able to do this formula if a countifs or sumifs function?
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?
ua-cam.com/video/bW6P2YvLyZg/v-deo.html
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.
hi is this still working? im having issue with 2nd menu or options
=QUERY(Data!A9:Z,"SELECT * WHERE 1=1 "&IF(D4="FIRST MENU",""," AND E = ('"&D4&"')")&IF(D5="SECOND MENU",""," AND F = ('"&D5&"') "),1)
ito shows an error or no display when SECOND MENU option was selected
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.
Try to sort by date and limit to 1.
@@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?
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.
Hi,
How we can create dropdown list for comma seperated value column?I created dropdown list with the help of your video but my column has so many comma seperated values.If I select a text from dropdown list only cells with that text alone is showing.for eg If I select car only cells with car will appear.but cells with car,bike won't appear.Also I have created dropdown list from three columns.How can I create query for that?
I'm having the same issue, did you figure out how to do this?
What to do, if I want to select all from South and West region ? Please suggest .
Example here ua-cam.com/video/qz3WIc_W2Mk/v-deo.html
How can we Increase Data Filter Data Range Automatically as we add data below sheet by importrange command?
Can I know how use this if my dropdown list is the date format, i followed the steps but show this error Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "10162023 "" at line 1, column 20. Was expecting one of: "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "and" ... "or" ... "*" ... "+" ... "-" ... "/" ... "%" ... "*" ... "/" ... "%" ... "+" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "*" ... "/" ... "%" ... "+" ... "-" ...
Hello! How can we fix result error too large? I've been watching your videos still couldn't find any solution from this. Need your help here. Thanks! btw I'm using Query with importrange functions.
Hi. Is there a way to keep the font format in the Query output? I'm missing bold words & colored fonts. Thanks.
No.
is it possible to make it so that there is a neutral state where no data is shown?
I found the solution! It's not elegant but it's just a simple "If" function. I made it so that if the drop down cell was = to "Name" then it would be displayed as blank, if it was any other information in the dropdown it would run the query function as normal.
considering you're using data validation for your control cells, why use the LOWER function? You're never going to have a case mismatch, as you're always pulling your options from your data list, right?
Maybe for tutorial purposes only. But yeah you can remove the Lower function
How do i make changes to the query if the Column C from Data is numbers and not text(Reps)?
Don't use single quotes around the value.
Learn Google Spreadsheets Thanks for the quick response.. I assume this removing quotes rule can be applied everywhere
you should skip the all region part to ease the explanation
Holy shit.. Can I do it with AI?
this is not working QUERY('Daily Stores'!1:999,"SELECT * WHERE 1=1 "&IF(A2="All Team"," "," AND LOWER(B) = LOWER('"&A2&"') ")&IF(B2="Store"," "," AND LOWER(C) = LOWER('"&B2&"') "),1)
This is by far the best instruction I have ever had from someone on google sheets. I am a firefighter instructor and you have a natural talent for teaching!!! The reason I say that is because you not only showed me how to use this formula but you did it in a way that taught me multiple ways of doing levels of formula. I actually needed both for my project so thank you very much!
Thanks David!
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!!
Great to hear!
hello, i did everything like you, but i have 3 IFS and query doesnt work. query:lower takes a text parameter. =QUERY('SUM DATA'!B4:I453;"SELECT E,F,G,H,I WHERE 1=1 "&IF(B2=".ZAMESTNANEC";"";" AND LOWER(C) = LOWER('"&B2&"')")&IF(C2=".ÚSEK";"";" AND LOWER(D) = LOWER('"&C2&"')")&IF(D2=".MESIAC";"";" AND LOWER(E) = LOWER('"&D2&"')");1)
Great video but the moment I use one of the drop downs I get Value error. Each one works fine individually, I'm tearing my hair out!
I am getting error while I am using the below expression.. I have 3 filters..Agent and start date and end date..Not sure where I am making the mistake
=QUERY(GrandTotalByDayByAgent!A3:F,"select A,B,C,D,E,F
WHERE 1=1 "&IF(G2="ALL AGENT", "AND " ," AND B = '"&G2&"' ")
& A>=DATE """&TEXT(B2,"yyyy-MM-dd")&""" AND A
@Learn Google Spreadsheets for the God's sake, can you please have a look and advise what is wrong here? Video is super helpful, I repeat the exact actions, but my Query function does not work - it just do not show any data, but there are no errors: =QUERY('Combined Data for Search W CONTAIN function'!A1:I457,"SELECT * WHERE 1=1 "&IF(A2="All Service types",""," AND LOWER(B) =LOWER('"&A2&"') ")&IF(B2="All Vertical types",""," AND LOWER(B) =LOWER('"&B2&"') "),1)
Wonderful presenting!!
Thank youvery much 👍
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 !!
:)
Hi, thanks for video, it was very usefull for me. But can you show, how can i sort filtered date by some column? The Query function is very complicated and i dont know, where can i put "order by" or "sort by". Thank for your answer.
When I use the formula after 1=1, I only get the first line (the one where it says Date, Region, ect. in the video). Before, when I used the formula shown in the beginning it was all there, but then when I changed it to the IF formulas, it stopped showing all the data? I dont know what I've done wrong
Hello Sir... Please reply me... My question... I have so many spread sheet (work book). I have a list for data validation any spreadsheet. How to use for data validation one spreadsheet workbook to another spreadsheet.
how to return cell address with XLOOKUP function of a last non-blank cell in google sheets?
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.
QUERY(Data!A1:F, "SELECT * WHERE 1=1 "&IF(A2="All Regions",""," AND LOWER(B) = LOWER('"&A2&"') ")&IF(B2="ALL Reps", "", " AND LOWER(C) = LOWER('"&B2&"') "), 1)
just as I was about to give up scrolling. =THANKS(B:R,1.000.000)
what if i have a drop down column that have "All Year" as default and all the other drop down are numbers?
Something on lines of drop down and data filtering I have a questions. Can someone help.
Hi ! Your video has been really helpful. I am a totally beginner and I tried to reproduce your method :
=QUERY(Dex!A2:F; "select * where 1=1 "&SI(A2="Toutes gen";"";" ET E ='"&A2&"') ")&SI(C2="Tous";"";" ET C ='"&C2&"') ")&SI(B2="Toutes versions";"";" ET F ='"&B2&"') ");1)
yet a #VALUE error message show up as follows :
QUERY : PARSE_ERROR: Encountered " "ET "" at line 1, column 21. Was expecting one of: "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "and" ... "or" ... "*" ... "+" ... "-" ... "/" ... "%" ... "*" ... "/" ... "%" ... "+" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "-" ... "*" ... "/" ... "%" ... "+" ... "-" ... .
What did I miss ?
I think the error is in here "') "), every IF contains a ") in the end
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.
I'm trying to modify this formula to access my data sheet that is located on a different spreadsheet and can't seem to get it to work. =query(IMPORTRANGE("docs.google.com/spreadsheets/d/1dDapWVRzaXaTaSlU4sVrMhYYEezY7zq9ctzfWVgs5hU/edit#gid=869459706","Charges!A2:F"), "&IF(B1 Contains 'Student Name',"", AND B = ('"&B1&"') 'select Col2, Col4, Col5"). Do you have any suggestions? Also, the URL needs to change based on what is in the dropdown box.
Once data is coming from a different spreadsheet, it must be treated as array. So you need to use Col1, Col2 syntax.
How to do this: I want the text from the adjacent cell to appear automatically If I call one cell as a reference. That is I want the content of the B2 cell to appear if I refer A1 cell. For eg. say A1 is having a date "Jun 01, 2021" and B1 has the text "English", I want to refer the A1 cell and want to display the B1 cell content, like C2 = if A1 = "Jun 01, 2021" then it is equal to B2 and print B2. I have OCD and this one is killing me... Please help with formulae. SO I can automatically make my subjects appear on the specific dates on the dates calendar I have created
VLOOKUP ?
can you give me access of this google sheet? please
Hello, thank you for sharing. I made a form in google drive with drop-down menu (in texts)in each cell and also with numbers. My question ishow can I get the average of the whole column with the exclusion of texts and just compute the average of the ones with number? Thank you in advace
Regular AVERAGE function ignores text.
using arrays with curly brackets and commas will that work?
How to we creat pivot with drop down and query functions pls help
Excellent video, it pretty much covers all my doubts..
Wow so complex and feeling challenged! Hehe
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?
Why'd you do that LOWER thing in the formula?
I'm trying to build a drop-down list that builds a quote based on drop-down qualifications. I feel like this is kind of the same thing going on here but I can't work it out..
Maybe this will help? ua-cam.com/video/NVpnyiwsu8c/v-deo.html
Tq sir, this very help me but one question. How to filter start date n end date work together with your filter? Can you help me.plzzz
There is an example in my Dashboard videos ua-cam.com/play/PLv9Pf9aNgemuhl6sIdURu9XliHN_TizfX.html
Super useful video 😊. Does anyone know how to add a limit? Whenever I try it messes up the whole formula
limit always goes to the end of the query
If the data contain ( ) or ' characters it breaks. How to update the query to address those cases? For example, if in C2 cell we have "Lance (isn't it)" instead of "Lance." Thank you!
Looking to add on more filter to this string, but finding no such luck. I would like to have the third filter run much the same using dates. I have tried adding another simplified edit as I know dates in query are touchy. The formula doesn't faul, but returns "N/A". Where can I find the next step?
Hoping there may be someone here that can help out.
HI, CAN WE USE THIS ALONG WITH DATE RANGE FILTERING? (Filter by Date Range using WHERE Statement Tutorial - Part 3) The problem is there will be 2 WHERE statements to join. Want to know is that possible with &
How can I sum transactions of a specific vendor for a month?
Hi! Very useful video, thanks. The formula worked only one time, but when I change the item in the drop down list, nothing happens. Can you explain me why?
Sir, I am unable to use query when I use numbers in as a reference in drop-down menu i.e when cell " " " & A2& " " " contains a number it is unable to pull the data
Numbers in QUERY statement are not wrapped in quotes.
where Col1 = '4' incorrect
where Col1 = 4 correct
@learngooglesheet
The method is just incredible but I want few things in addition to this
(1) Can I use multiple "AND" function for filtering data with multiple columns within Query Function if yes How? As I have tried the same but I received an error.
(2) By applying this tutorial method in addition to this I want you use ORDER BY function to sort the data with a particular column. I tried the same but I got an error.
So can you please help me out in this.
Thanks in Advance 😊
You are simply AMAZING! Thanks for sharing that!
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
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.
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
Your videos are bloody amazing, you should see some of the stuff I've made solely with lessons from your videos.
Thank you
Sir, can i send the sheet name also as drop down in query function as range if 3 sheets have the same columns
If I understand what you're asking then yes.
Let's assume your Sheet name Dropbox is in cell A1
Using the range from the video, in place of Data!A1:F put INDIRECT(A1&"!A1:F")
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#?
This has been a GREAT help!!!! I get to the very end and I keep getting a "Error: Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " ")" ") "" at line 1, column 60. I'm pretty sure it's because my 2nd dropdown is a date and was VERY difficult to develop. It all works separately, but when I try to combine the three (3), it fails (at 13:30 in your video). I'm GREAT until I paste the "AND" part into the Query. Is it possible that you can see where the error is?? =QUERY(Contact!$A$1:$P,"SELECT A,F,O,P,J,G,H WHERE 1=1 "&IF(D5="All Regions",""," AND F = '"&D5&"') ")&IF(E5 ="Select",""," AND J = DATE '"&TEXT(E5,"YYYY-MM-DD")&"')"))
Where you have &D5&"') ") remove the ) closet to the second &.
Should look like this &D5&"' ")
The same for after &TEXT(E5,"YYYY-MM-DD")&"') "))
Should look like this
&TEXT(E5,"YYYY-MM-DD")&"' "))
Hopefully that is understandable and works for you.
"Whatever that little thing is"... It's basically a Table/Grid/Cells, ie, Your "Range" Selector. You're welcome. Great Video by the way.
When the drop down cell is empty, nothing shows? How can we apply the same display as showing (ALL) when any of the drop downs is empty? ~ Also ~ how can we show fractions in the drop down?
Holy crap man your videos helped me so much tonight! I almost can't believe I learned as much as I did. Thank you!
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)
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?
Hey thanks for all your info! Maybe could point me in the right direction. I am a teacher and I have equipment that people borrow and sometimes never return... I have created a check out system using QR codes that are attached to google forms. But I am trying to figure out how I can I can have the google form sheet delete/add to my "inventory" sheet. So for example on my google form if some one checks out 1 black marker, 3 green pencils, and 7 soccer balls how can I see up to date info on my inventory list. Then also going the other way when it is returned? Thanks so much for your time and help!
It's hard to say without knowing your setup & layout but I would check SUMIFS function.
@@ExcelGoogleSheets Thanks Ill check it out!
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)
Sir I want to add date filter in the formula
How do I in Google Spreadsheets - which maintain hyperlink formatting on import importRange + QUERY?
Excellent tutorial. Very detailed and easy to replicate. Thank you.
Thank You!
Loveeeee. You made my day. All the data were well filtered. It was very easy to get information of my Company much quicker!!
I like your work, but this really is the hard way to utilize drop downs to filter data. You can reference the data in the drop down,.
how can i create a “Reset” Button?
❤❤❤❤❤❤ It solved my two days problem
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!
Teacher... my Brain Hurts!
When I transfer data from one file in Google Sheet to another file with (= QUERY (IMPORTRANGE), the row links do not move to the new rows?
Thank you for this. Is there a way that we can select 2 or more regions or 2 or more reps, not ALL only?
thanks man very much , your step-by-step instruction is super helpful
Thanks!!! Really helped me gaining an internship
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. :)
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)
Superb! thanks...
Can we get the link of this spreadsheet
I wanted this tutorial with a single dropdown :(
Good job tho, learnt a lot!👍