Google Sheets Query Formula with Dropdown List (Data Validation) Cell Reference - Dynamic Dashboard
ฝัง
- เผยแพร่เมื่อ 1 มิ.ย. 2024
- Doc from video here: docs.google.com/spreadsheets/...
Go to File, Make a Copy
Schedule a consultation call with me!: clarity.fm/hustlesheets
![[TH] 2024 PMSL SEA GF D1 | Summer | แข่งที่ไทย ต้องใส่ให้สุด](http://i.ytimg.com/vi/Jkuf7nCS7jI/mqdefault.jpg)
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!
finally found the video I was looking for, the pace of your video was perfect, not to fast and simply to follow. Thanks
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
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!
The only video that explains this in an understandable way for me! Thank you so much!
This video helped me figure out how to set up dynamic references to review data. Thank You - very informative and easy to follow
Thank you so much!! I spent all day watching videos trying to figure this out and you solved it for me! Thank you 😘
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!
This is exactly what I needed, thanks! Great job
Nice and clean. Anyone can maintain your work.
This is exactly what I needed, thank you so much for the great explanation on this. It was super easy.
You're very resourceful Michael, it really help us. Thank you so much.
This is what I've been looking for. Brilliant! Thanks.
You are awsome, I have been looking for this for a week
Love your video. very clear. loved the way you showed your errors. your speech was clear. Thank you
Excelente, al fin un vídeo de utilidad para mi!!! Gracias
Hey! thank you so much! this is what I've been searching for a long time. Exactly what I want the most.. Great Job!
great video! Showed exactly what I needed to know and very clear.
TQ so much Michael !!!! I really appreciate all your guidance and tutorial. Your tutorial very clear and good .
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.
Sir love u 3000 u have explain the same thing which i was searching since long back.
Thank u so much sir
This is awesome and very useful! Thank you so much for your sharing!
Impressive! Thanks for the inspiration!
I have been looking for since long. Thanks I will definitely use it. Infect I needed it desperately
Really want to say thank you for this helpful tutorial video.
Excellent! you presented what I needed..thanks
Thanks for this video, this is a game changer and a life saver!
This video helped Me Out so much today! 😭 Thank you
Thank you so much. Very good explanation for the beginner. This helps me a lot.
Great video - really good.!Thank you
Thank you very much man! This worked for me !!!
thank you! Clear explanation
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.
Hey brother, how do we got about pulling blank cell references or adding "all" to the drop down list?
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?
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 very much. Nailed.
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&""" "
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?
Jeepers! Which tab are you on each time you put in settings?
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?
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
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.
very thank for your Video
Very Helpful
This is helpful 👍
Very Good.. just let me know how to get the date in between the date range using cell reference in Query function
Nice!
thank you save my life also my eyes got confused on the '"&$B1&"' part
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?
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?
Very Helpful Tutorial How can we Filter by date ?
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.
Thannnnkkk youuu!!!!!
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
Bro thanks a ton...
thank you
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.
Thanx very much sir
thanks !
Thanks for supporting my channel Cesar!
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!
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 u
10:52 very important
How do use the WILDCARD "*" to select ALL choices of a given criteria?
Thanks
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
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?
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 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.
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" ...
What if I have 4 conditions? Can I use 3 and? Or how?
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.
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!
Where is the link for the next part of this video tutorial?
How can I be sure I have the hole list. I've tried it, and I mis some information using this formula.
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?
Please send me the link for query functions with date and other data
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?
Can you make a web app to do this?
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)
cool but the link is not the sheet from the video
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
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
=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&””” “)
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
Why does some of my cell show #REF or #N/A
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