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 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!
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.
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.
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.
I know this is an old post, but I love this. This is almost exactly what I'm trying to do. I'm wondering, is it A - possible to query from multiple data ranges across multiple sheets? For example, I have three stores with varying sales data on three different sheets. Is it possible to Query and have one of the data validation drop down filters be the store name ie- store 1, store 2, store 3, and all stores? And B - is it possible to create a data validation filter that allows me to select a date range? If I have weekly store sales in columns on each of my store's sheets (ie - week 1, week 2, etc), could I potentially set up a formula that would allow me to select say week 1-5, or week 3-9, and so on?
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 !!
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
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.
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!
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)
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.
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?
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!
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#?
great stuff thanks a lot. how do I query values such as 3.5. I know the LOWER command is only for text. So I removed LOWER but it returns only integers like 1,6, 20 etc.. What do I have to adjust to get other kinds of numbers back? My data is very long and has various characteristics. how do I get the data sorted in the drobdowns?
@@ExcelGoogleSheets thanks, but it dont have quotes in this case, i will watch the other vids as well - maybe i will found the problem after watching it ;)
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
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")
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.
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?
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 &
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.
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.
@@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.
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
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?
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, 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?
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
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.
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)
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..
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?
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!
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!
Awesome, you show a different way from others. This is what I'm looking for.
This is really easy to follow and understand the process. Thank you very much for sharing
👍
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!
Thanks!
Thank You!
I was figuring this out for 3 hours and this vid finally got me to where i needed to go.
thank you so much for breaking down this mysterious language of spread sheet filtering. you're really good at this!!!
Loveeeee. You made my day. All the data were well filtered. It was very easy to get information of my Company much quicker!!
"1=1" that's what I needed. Well worth watching the video!
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.
Wonderful presenting!!
Thank youvery much 👍
Thank you very much, your explanation is very easy to understand
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.
Thanks!!! Really helped me gaining an internship
thanks man very much , your step-by-step instruction is super helpful
Amazing tutorial for a beginner to such stuff! Thanks mate. I loved it.
Excellent video, it pretty much covers all my doubts..
very well explained step by step
this one is really helpful. Thank you!
Thank you soooooo much for this tutorial. It just helped me finish my task!
Excellent tutorial. Very detailed and easy to replicate. Thank you.
Thank You!
Thank you so much for this tutorial.
You are simply AMAZING! Thanks for sharing that!
THIS IS FANTASTIC! THANK YOU SOOOOOO MUCH!!!!!!!!
Very helpful - THANK YOU!!
Holy crap man your videos helped me so much tonight! I almost can't believe I learned as much as I did. Thank you!
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!
It's really help me to solve my problem. Thank you very much.
👍
Can i make the data range as variable value?
Yes. I have something like that in Dashboard series.
Great help to learn 👍
"Whatever that little thing is"... It's basically a Table/Grid/Cells, ie, Your "Range" Selector. You're welcome. Great Video by the way.
Amazing content. Thanks
How do you know what i want next in google sheet,😍 U r reading my mind...
Thanks so much, all your videos very useful
Best g sheets professor on youtube. Pls continue doing the g sheets web systems with js videos
Simply Wonderful
You're awesome! Thank you for the help!😁
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
❤❤❤❤❤❤ It solved my two days problem
Thank you for this. Is there a way that we can select 2 or more regions or 2 or more reps, not ALL only?
Thank you so much! I was able to use this filter for my work! Kudos!
Love your content man, you've taught me a lot
Great to hear!
Thank you for the great titorial. How to add a function to show me if I have blanks in region and reps?
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 ?
very nice explanation
Thanks!
I know this is an old post, but I love this. This is almost exactly what I'm trying to do. I'm wondering, is it A - possible to query from multiple data ranges across multiple sheets? For example, I have three stores with varying sales data on three different sheets. Is it possible to Query and have one of the data validation drop down filters be the store name ie- store 1, store 2, store 3, and all stores? And B - is it possible to create a data validation filter that allows me to select a date range? If I have weekly store sales in columns on each of my store's sheets (ie - week 1, week 2, etc), could I potentially set up a formula that would allow me to select say week 1-5, or week 3-9, and so on?
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 !!
:)
using arrays with curly brackets and commas will that work?
so nice! hope i can copy you in a just a single sitting haha
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.
Great video, helped a lot. I was looking just for this!
Superb! thanks...
You're The Best!
are you able to do this formula if a countifs or sumifs function?
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.
I wanted this tutorial with a single dropdown :(
Good job tho, learnt a lot!👍
Thank you so much!!!
Your videos are bloody amazing, you should see some of the stuff I've made solely with lessons from your videos.
Thank you
Nice trick using '1=1' to construct the query string
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!
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)
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.
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?
Wow so complex and feeling challenged! Hehe
Thank you so much...
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?
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!
Wonderful Bro you solved all of my problems :)
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?
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#?
great stuff thanks a lot. how do I query values such as 3.5. I know the LOWER command is only for text. So I removed LOWER but it returns only integers like 1,6, 20 etc.. What do I have to adjust to get other kinds of numbers back?
My data is very long and has various characteristics. how do I get the data sorted in the drobdowns?
No quotes around numbers, instead of '1' use 1. Watch my QUERY series to see how to function works.
@@ExcelGoogleSheets thanks, but it dont have quotes in this case, i will watch the other vids as well - maybe i will found the problem after watching it ;)
@@fuhfux23 did it found any solution ??
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
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")
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.
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?
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 &
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.
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.
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
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?
what if i have a drop down column that have "All Year" as default and all the other drop down are numbers?
Do you know how to choose multiple items from a drop down and the value will show the data of both items on the pivot table?
Slicer? ua-cam.com/video/1BYRTtPLDVQ/v-deo.html
@@ExcelGoogleSheets 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.
Beautiful
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?
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
I think this is what I need? however can I get data from multiple sheets?
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.
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)
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
How can we Increase Data Filter Data Range Automatically as we add data below sheet by importrange command?
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
Check my dashboard series. There is an example there.
amazing, thank you so much
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?
how can i create a “Reset” Button?
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!
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?
thanks
But why does the Function repeat or collect the data in the same row
Can we select only few particular cells(within a column) as a list of range for data validation?
Hi, I admired