I've been trying to figure out how to do some of these functions for weeks - your video was the ONLY one I found that made it straight forward, easy to understand and showed me exactly how to customize the QUERY function for my needs. Subbed! Thanks so much!
This video was super informative and helpful! One thing to mention is that the Query function will be case sensitive on your source data. You probably mentioned that in the video somewhere and I just missed it. Once I figured that out, my formulas worked great! Thanks for the help!
Thank you! English is a second language that I'm still learning and I didn't know anything about formulas and had a lil knowledge of sheets or Excel, now that I'm working on my personal business plan I have to learn everything by myself, from sheets to web development doing this solo project, your resources of knowledge are very good and your are a excellent teacher very confident at the time you speak. Thank you very much. Good luck with this Channel. I hope with get more videos like this one
You save my life. Stuck in the 3 quotations for half a day. Still don't know why it use 3 but my teacher use single and double quote that make me confused.
Thanks - very clearly explained. This method, however, means that the filter is obligatory. I'd prefer the option to have all the data or the filtered data. I guess that means including the "WHERE column =" in the text to concatenate. Including more than one drop-down makes everything even more complicated!
You brought me here to comment which I usually don't!! Damn helpful!! Thanks a lot!! Just one thing, where you wrote D="""&B2&""" ", it didn't work for me so instead D, I used Col4="""&B2&""" "
cool, I know there is a way to have multiple dropdowns and reference them, but is there a way to have query ignore the AND string if the referenced cell is kept blank in the dropdown menu and skip it to the following(s) strings?
How do I add a "blank" filter- For example, if I want to see all of the home and away games with scores over 100? I don't need the dropdown menu to have anything listed. The Arena will need to have a blank option. How is that done?
Can you please put the complete file where you also show the first example with only one filter. Im having trouble getting it to work and I would appreciate having the reference file.
Thanks for your help, this was so useful. Can you pease help with one question, I am using drop down boxes to filter results but need an option to revert back to seeing all data, is there a way to do this without having to remove data validation? Example, I select a quarter from my drop down box but then want to be able to see data for all quarters? I can't figure out how to do this. Thanks again for extremely useful content!
Hello - Thanks for sharing. Wondering if this still works if you have nothing selected in any drop-down menu filters…? I have a dashboard with two query’s convinced with array brackets/semicolon. But can’t seem to get the multiple optional drop down filters to work with the query. The data comes from two different tabs and the original location is organized different as far as Columba go. Not sure what I’m missing in query to make multiple query’s in array work dynamically with either none, some or all filter drowpdowns selected
How do you keep a filter with all the options and the user can keep something blank but the other info will show? Example if all I want to see is the teams and win loss but keep my other filters active and not re write the query all the time?
I need an advice please: let say I have 10 units of one product with the same lot numbers. When I scan them the scanning system groups them in 1 line with a total=10. Then I need to copy and paste it into Google Sheet. But I need a solution how Google Sheet can break the pasted data into 10 individual lines just to be able to track each single unit if needs arise... something like if I scan those 10 units and pasted into the Google Sheet it breaks the pasted data into 10 individual lines...is that possible?
WHEN I USE THIS FORMULA ITS SHOW ERROR AND THIS TYPE NOTE SHOW ...... Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " ")" ") "" at line 1, column 22. Was expecting one of: "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "and" ... "or" ...
Hi, great video thank you!. I have 2 question please. If I have in my data validation dates that would be with one or three quotations marks? and if I need to use the counta or count formula with order by and desc limit what would be the order to put those things in your excercise?, after all you did with WHERE?
Can we create a drop down list in a manner that if that specific item is selected, other cells start reflecting data associated to that specific cell, like if I select Q4 from drop down list , it starts reflecting values of Q4 in other cells such a revenue, closed amount, cost etc?
I have one data validation range I want to use and 3 different criteria ranges(located in three different columns). Is there any way to include all three criteria ranges to that one data validation range? I spent an hour trying to find a solution, but I have yet to find someone who has covered this specific situation. I would really appreciate some advice. Best regards!
Hi, I was able to achieve the results of your two drop down combined selection. I need help as in: if I would like to have multiple selection in the second drop down (google sheets). I had used a one drop down menu with the following formula and it worked awesome and gave me multiple results. Could you help incorporate the second (multiple selection option) along with the Frist drop down. My first drop down is in A2 My second drop down is in A3 where I would like to have the following formula =QUERY(A33:X60, "SELECT * WHERE " & JOIN(" OR ", ARRAYFORMULA("A = '" & TRIM(SPLIT(A3, ",")) & "'")), 0)
Is it possible to add an "And OR" statement into the query formula in case you only wanted to use one of the filters but also want the option to filter more as needed?
This is super helpful! Thank you so much for sharing this :) I got one question - i receive an error when i tried with 'Date' dropdown in Query. Do we have different approach to filter by 'Date' or i'm missing sth? Thank you!!!
Very useful tips ,but i have actually an issue , every time i'm using the formula it shows on my first column on top the formula instead of the name of the group i choose. Using google sheets and it is really frustrated as i checked everything and i cant,deal it with .
like instead of date it shows the formula which it never happen before. Usually it will shows error or something else ,but the formula actually working ,only issue is in the first top cell that actually should convert once it hit enter
Thank you for the tutorial. Can you also explain, how to get data WITHOUT THE IF FUNCTION when cell reference is empty. For eg. Select Arena is the cell (B3) value, but Score can be either the cell (B4) value or all values. I tried going through the comments trail but could not get an answer.
@@marouanehajoui6491 Thank you for the reply. I'll rephrase my question. I want to get data through query where I have multiple criteria, but I may choose to skip a criteria in spite of it being in the formula. Like my formula factors Team, Arena and Score, but I may choose to keep/skip Arena.
good stuff man. what is the syntax to bring back the number of rows from the select query statement. i was trying count() but it is not working. if the select brings back 10 rows, i want to create a cell to display 10 for the search result. thanks.
You can have a cell dedicated to display the number of rows above the query and that cell you can use the =counta formula: =counta (A2:A). Leaving it at A2:A instead of for for example A2:A50 so the row count could be dynamic.
I am trying to develop an tv series watch list. searchable by series . series and season and a date watched. I want to do something similar to this. but updating the date is the tricky part.
Cool stuff. Any idea on how to wrap query formula with references into an array formula in GoogleSheets? Something like this (but the references are not working properly for this formula): =ARRAYFORMULA(IF(A5:A="","", JOIN(", ",UNIQUE(QUERY({INDEX(T_no_headers,,MATCH($A$3,Headers,0)),INDEX(T_no_headers,,MATCH($C$3,Headers,0))},"SELECT Col2 WHERE Col1="""&A5:A&"""",0)))))
You cannot edit any of the results of a query formula. If you want to make edits, you need to go back to the original data set and make changes there. I suggest you use the Filter view button if you're trying to filter data and then make changes
Formula? Which one? I'm using the QUERY formula. For your other question (I couldnt find the comment on the video) about the calendar - it's data validation. Right click and select data validation then change the criteria to DATE
@@hustlesheets lol.. I deleted the other question because I already found the answer.. hahaha.. About the above question, I am making a sheet where I would like to have 4 criterias (1 for names, 2 for the dates, and 1 for roles). I copied your query formula but when there is a criteria that is missing, there are no results showing. For example, I have ... -Name : KIM -Start date: Oct 05, 2020 -End date: Oct 10, 2020 -Role: Auditor (btw, there are 3 roles) What I am trying to say is that, I want all data for all the roles I worked to show when I didn't ticked any criteria for it. hahaha.. Hope you get my point, lol. Thank you for your response. :)
@@hustlesheets OH... WAIT WAIT WAIT!!!! I fromulated other! lol =QUERY('REV 1'!B2:G, "SELECT B,D,E,F,G WHERE B >= DATE """&TEXT(C6,"yyyy-MM-dd")&""" AND B
@@hustlesheets Lastly... Ended up with... =QUERY('REV 1'!B2:G, "SELECT B,D,E,F,G WHERE 1=1 " &IF(C9= "",""," AND D = """&C9&""" ") &IF(C5= "",""," AND C = """&C5&""" ") &IF(C6= "",""," AND B >= DATE """&TEXT(C6,"yyyy-MM-dd")&""" ") &IF(C7= "",""," AND B
I've been trying to figure out how to do some of these functions for weeks - your video was the ONLY one I found that made it straight forward, easy to understand and showed me exactly how to customize the QUERY function for my needs. Subbed! Thanks so much!
This video was super informative and helpful! One thing to mention is that the Query function will be case sensitive on your source data. You probably mentioned that in the video somewhere and I just missed it. Once I figured that out, my formulas worked great! Thanks for the help!
Thank you! English is a second language that I'm still learning and I didn't know anything about formulas and had a lil knowledge of sheets or Excel, now that I'm working on my personal business plan I have to learn everything by myself, from sheets to web development doing this solo project, your resources of knowledge are very good and your are a excellent teacher very confident at the time you speak. Thank you very much. Good luck with this Channel. I hope with get more videos like this one
The only video that explains this in an understandable way for me! Thank you so much!
finally found the video I was looking for, the pace of your video was perfect, not to fast and simply to follow. Thanks
You save my life. Stuck in the 3 quotations for half a day.
Still don't know why it use 3 but my teacher use single and double quote that make me confused.
Thank you so much!! I spent all day watching videos trying to figure this out and you solved it for me! Thank you 😘
Thank you so much! Your tutorials are super helpful. You go at a nice pace and you're very clear on your explanations. You've taught me a lot!
Thanks - very clearly explained.
This method, however, means that the filter is obligatory. I'd prefer the option to have all the data or the filtered data. I guess that means including the "WHERE column =" in the text to concatenate.
Including more than one drop-down makes everything even more complicated!
Love your video. very clear. loved the way you showed your errors. your speech was clear. Thank you
This video helped me figure out how to set up dynamic references to review data. Thank You - very informative and easy to follow
Sir love u 3000 u have explain the same thing which i was searching since long back.
Thank u so much sir
TQ so much Michael !!!! I really appreciate all your guidance and tutorial. Your tutorial very clear and good .
You brought me here to comment which I usually don't!! Damn helpful!! Thanks a lot!! Just one thing, where you wrote D="""&B2&""" ", it didn't work for me so instead D, I used Col4="""&B2&""" "
You are awsome, I have been looking for this for a week
Nice and clean. Anyone can maintain your work.
Thank you so much. Very good explanation for the beginner. This helps me a lot.
This is exactly what I needed, thank you so much for the great explanation on this. It was super easy.
You Deserve a comment👋
This is exactly what I needed, thanks! Great job
I have been looking for since long. Thanks I will definitely use it. Infect I needed it desperately
Hey! thank you so much! this is what I've been searching for a long time. Exactly what I want the most.. Great Job!
You're very resourceful Michael, it really help us. Thank you so much.
great video! Showed exactly what I needed to know and very clear.
Terimakasih, tutorial yang sangat berharga, sayang tanggalnya belum sempat dibahas
This is what I've been looking for. Brilliant! Thanks.
This video helped Me Out so much today! 😭 Thank you
cant really see it on the phone, reco you to focus more in screen, dont need to show the entire screen. But something id say your formula works well.
Really want to say thank you for this helpful tutorial video.
Hey brother, how do we got about pulling blank cell references or adding "all" to the drop down list?
thank you save my life also my eyes got confused on the '"&$B1&"' part
This is awesome and very useful! Thank you so much for your sharing!
Excelente, al fin un vídeo de utilidad para mi!!! Gracias
cool, I know there is a way to have multiple dropdowns and reference them, but is there a way to have query ignore the AND string if the referenced cell is kept blank in the dropdown menu and skip it to the following(s) strings?
Excellent! you presented what I needed..thanks
How do I add a "blank" filter- For example, if I want to see all of the home and away games with scores over 100? I don't need the dropdown menu to have anything listed. The Arena will need to have a blank option. How is that done?
Thanks for this video, this is a game changer and a life saver!
10:52 very important
Genius! Thanks much
Can you please put the complete file where you also show the first example with only one filter. Im having trouble getting it to work and I would appreciate having the reference file.
Great video - really good.!Thank you
Thanks for your help, this was so useful. Can you pease help with one question, I am using drop down boxes to filter results but need an option to revert back to seeing all data, is there a way to do this without having to remove data validation? Example, I select a quarter from my drop down box but then want to be able to see data for all quarters? I can't figure out how to do this. Thanks again for extremely useful content!
I have this exact problem as well. Did you find a solution?
@@rawsonleavitt4501 Sorry but I did not, still having the same problem
@hustlesheets Any advice?
Thanks Steve!
Maybe you can try this:
docs.google.com/spreadsheets/d/1J6RuqgrTgN5k_W-P4_Zy3OIwLUpM9sL3kUVKpeM9cZ4/edit?usp=sharing
Hello -
Thanks for sharing. Wondering if this still works if you have nothing selected in any drop-down menu filters…? I have a dashboard with two query’s convinced with array brackets/semicolon. But can’t seem to get the multiple optional drop down filters to work with the query. The data comes from two different tabs and the original location is organized different as far as Columba go. Not sure what I’m missing in query to make multiple query’s in array work dynamically with either none, some or all filter drowpdowns selected
How do you keep a filter with all the options and the user can keep something blank but the other info will show? Example if all I want to see is the teams and win loss but keep my other filters active and not re write the query all the time?
Thank you very much man! This worked for me !!!
I need an advice please: let say I have 10 units of one product with the same lot numbers. When I scan them the scanning system groups them in 1 line with a total=10. Then I need to copy and paste it into Google Sheet. But I need a solution how Google Sheet can break the pasted data into 10 individual lines just to be able to track each single unit if needs arise... something like if I scan those 10 units and pasted into the Google Sheet it breaks the pasted data into 10 individual lines...is that possible?
Impressive! Thanks for the inspiration!
WHEN I USE THIS FORMULA ITS SHOW ERROR AND THIS TYPE NOTE SHOW ......
Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " ")" ") "" at line 1, column 22. Was expecting one of: "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "and" ... "or" ...
Thank you so much.
I followed these steps exactly but only the headers are pulling through and not the actual data. Any assistance with this please?
Hi, great video thank you!. I have 2 question please. If I have in my data validation dates that would be with one or three quotations marks? and if I need to use the counta or count formula with order by and desc limit what would be the order to put those things in your excercise?, after all you did with WHERE?
thank you! Clear explanation
Can we create a drop down list in a manner that if that specific item is selected, other cells start reflecting data associated to that specific cell, like if I select Q4 from drop down list , it starts reflecting values of Q4 in other cells such a revenue, closed amount, cost etc?
Super awesome! Thanks so much. I'm curious how you would give the user the ability to select the columns (by name) that they want as well.
How can I be sure I have the hole list. I've tried it, and I mis some information using this formula.
How do use the WILDCARD "*" to select ALL choices of a given criteria?
Thank you very much. Nailed.
I have one data validation range I want to use and 3 different criteria ranges(located in three different columns). Is there any way to include all three criteria ranges to that one data validation range? I spent an hour trying to find a solution, but I have yet to find someone who has covered this specific situation. I would really appreciate some advice. Best regards!
Hi, I was able to achieve the results of your two drop down combined selection. I need help as in: if I would like to have multiple selection in the second drop down (google sheets).
I had used a one drop down menu with the following formula and it worked awesome and gave me multiple results.
Could you help incorporate the second (multiple selection option) along with the Frist drop down.
My first drop down is in A2
My second drop down is in A3 where I would like to have the following formula
=QUERY(A33:X60, "SELECT * WHERE " & JOIN(" OR ", ARRAYFORMULA("A = '" & TRIM(SPLIT(A3, ",")) & "'")), 0)
Very Good.. just let me know how to get the date in between the date range using cell reference in Query function
Is it possible to add an "And OR" statement into the query formula in case you only wanted to use one of the filters but also want the option to filter more as needed?
Thanks for the tutor, it is helpful :) By the way, the link is wrong, could you share the correct one?
hmm I think this is the correct one!
This is super helpful! Thank you so much for sharing this :) I got one question - i receive an error when i tried with 'Date' dropdown in Query. Do we have different approach to filter by 'Date' or i'm missing sth? Thank you!!!
=QUERY(Dashboards!A5:G252,"Select * where A ="""&B3&""" ") please where is the problem
Very Helpful Tutorial How can we Filter by date ?
Jeepers! Which tab are you on each time you put in settings?
Very useful tips ,but i have actually an issue , every time i'm using the formula it shows on my first column on top the formula instead of the name of the group i choose. Using google sheets and it is really frustrated as i checked everything and i cant,deal it with .
like instead of date it shows the formula which it never happen before. Usually it will shows error or something else ,but the formula actually working ,only issue is in the first top cell that actually should convert once it hit enter
Very Helpful
Thank you for the tutorial. Can you also explain, how to get data WITHOUT THE IF FUNCTION when cell reference is empty. For eg. Select Arena is the cell (B3) value, but Score can be either the cell (B4) value or all values. I tried going through the comments trail but could not get an answer.
=QUERY(Dashboards!A5:G252,"Select * where A ="""&B3&""" ") please where is the problem
@@marouanehajoui6491 Thank you for the reply. I'll rephrase my question. I want to get data through query where I have multiple criteria, but I may choose to skip a criteria in spite of it being in the formula. Like my formula factors Team, Arena and Score, but I may choose to keep/skip Arena.
good stuff man. what is the syntax to bring back the number of rows from the select query statement. i was trying count() but it is not working. if the select brings back 10 rows, i want to create a cell to display 10 for the search result. thanks.
You can have a cell dedicated to display the number of rows above the query and that cell you can use the =counta formula: =counta (A2:A). Leaving it at A2:A instead of for for example A2:A50 so the row count could be dynamic.
I have a specific question, but I cant see how to get in touch for a consult since your link doesnt work.... can you contact me?
This is helpful 👍
I am trying to develop an tv series watch list. searchable by series . series and season and a date watched. I want to do something similar to this. but updating the date is the tricky part.
very thank for your Video
Let's say I have 100+ tabs, but the tabs are all numbered 1, 2, 3, .... 112, ... how can I add all these tab name to the quarry function?
You should create all of the tabs before hand, perhaps hide the ones you don't need yet. Then just add all of the tab names into the formula
thanks !
Thanks for supporting my channel Cesar!
cool but the link is not the sheet from the video
Do you guys know if there's a way to edit the data that query formula gives you so that it updates it on the original list too?
two way sync isn't something Google Sheets is good at doing :( Maybe look into Airtable?
Where is the link for the next part of this video tutorial?
Nice!
Thanx very much sir
thank u
What if I have 4 conditions? Can I use 3 and? Or how?
Thannnnkkk youuu!!!!!
Bro thanks a ton...
Please send me the link for query functions with date and other data
Cool stuff. Any idea on how to wrap query formula with references into an array formula in GoogleSheets?
Something like this (but the references are not working properly for this formula):
=ARRAYFORMULA(IF(A5:A="","", JOIN(", ",UNIQUE(QUERY({INDEX(T_no_headers,,MATCH($A$3,Headers,0)),INDEX(T_no_headers,,MATCH($C$3,Headers,0))},"SELECT Col2 WHERE Col1="""&A5:A&"""",0)))))
thank you
how can i edit the filtered data..
You cannot edit any of the results of a query formula. If you want to make edits, you need to go back to the original data set and make changes there. I suggest you use the Filter view button if you're trying to filter data and then make changes
=QUERY(Stock!A2:E" SELECT E WHERE A = """&I2:I&""" ")
is there any error? please help me.
=QUERY(Stock!A2:E” SELECT * WHERE A = “””&I2:I&””” “)
Why does some of my cell show #REF or #N/A
Thanks
What is the formula when there's a criteria missing??
Formula? Which one? I'm using the QUERY formula.
For your other question (I couldnt find the comment on the video) about the calendar - it's data validation. Right click and select data validation then change the criteria to DATE
@@hustlesheets lol.. I deleted the other question because I already found the answer.. hahaha..
About the above question, I am making a sheet where I would like to have 4 criterias (1 for names, 2 for the dates, and 1 for roles). I copied your query formula but when there is a criteria that is missing, there are no results showing.
For example, I have ...
-Name : KIM
-Start date: Oct 05, 2020
-End date: Oct 10, 2020
-Role: Auditor (btw, there are 3 roles)
What I am trying to say is that, I want all data for all the roles I worked to show when I didn't ticked any criteria for it. hahaha..
Hope you get my point, lol.
Thank you for your response. :)
@@hustlesheets OH... WAIT WAIT WAIT!!!!
I fromulated other! lol
=QUERY('REV 1'!B2:G, "SELECT B,D,E,F,G WHERE B >= DATE """&TEXT(C6,"yyyy-MM-dd")&""" AND B
@@hustlesheets Lastly... Ended up with...
=QUERY('REV 1'!B2:G, "SELECT B,D,E,F,G WHERE 1=1 " &IF(C9= "",""," AND D = """&C9&""" ") &IF(C5= "",""," AND C = """&C5&""" ") &IF(C6= "",""," AND B >= DATE """&TEXT(C6,"yyyy-MM-dd")&""" ") &IF(C7= "",""," AND B
Can you make a web app to do this?