How to Create Dropdown Filters on Google Sheets Dashboard Using QUERY Formula (ADVANCED TRICK)

[@hustlesheets]

I'll be releasing a full scale paid dashboard course soon! Signup if you want early access (will be limited to x amount of students): forms.gle/iPPQtHjP4GF6ukgAA

[@brianyhan]

1 year ago I was unemployed, sleeping on couches, eating bootleg cereal for dinner. No skills, no food, no money. Hustlesheets gave me a job. Now I have a house, a wife and used Nissan Altima. Thank you Hustlesheets!

[@bikebro7747]

Amazing! What job were you able to get?

[@HoopsMR22]

Dude, I have spent a week now trying to figure out how to filter a search query by two different drop down boxes. I tried nesting three if functions within each other and was losing my mind trying to get it to work. Making those helper columns never dawned on me. It's genius, but it's so simple. This video is amazing. Thank you for making it and for the KZbin algorithm for recommending it to me.

[@prilyang16]

This is what I've been trying to do and it took me a long time to find the perfect tutorial 😭 and now I found it! I really want to enhance my skills with Google sheets. Thank you!

[@LunarchieMoon]

Exactly what i wanted to learn since a long time😭 Thanks, this helps me a lot! For real. May God bless you for always helping and sharing your insights to each other 🙏🏻✨

[@AvalonCakes]

Thanks!

[@juangoodtrade5571]

Is there a way to enable multiple selection in the filter

[@AshutoshKumar-vo6xv]

Hey I have the same issue did you found any solution yet

[@thepoortraveler]

Thank you for making my life easier! Is there a way where I can change the data in the Dashboard sheet and it will update the Sales Data sheet?

[@hustlesheets]

No this cannot be done without apps script and even then I do not recommend it. The Dashboard sheet is purely for visualization and Sales Data is where all data input should be.

[@VaughanPeters]

Is there any way to use the filter formula to return data to a new sheet, edit it, and have the edits auto reflect on the source sheet?

[@NbaLive4ever]

Great stuff man!! I’ve been diving deep into this lately and took a while to understand, but after more research and exposure, this makes more sense the second time watching it! Thank you!

[@LauraD228]

Thanks for adding videos with filters that make dashboards interactive!😊

[@mattallenclosedforum9505]

Thank you for using the office to make this easier to get through :)

[@eastcoastdamost]

Bro, you are the master. This really helped me with my metrics. Big ups!

[@pauldeleon7438]

Your work is really awesome. Its very helpful and easy to understand, but it would be much better ( i think) if you added a daily tracker aside from monthly or explained it somehow if someone wants it on daily and monthly. If someone want to see the sales amount of agent from an exact date

[@alearnedlife]

So clean! I’m impressed

[@thehandmadebazaar1824]

Amazing video, how about if the data in the column is words that you need to count. How you can edit the formla to work with words? Thanks

[@alaeismael6263]

That look awesome well done , I just want ask you if you can make the same idea with just month's like a quarter report data to and make the graph proactive

[@scottfrost2855]

Thank you so much for your video. I have created many dashboards using what you showed, but I didn't know some of these tricks. I always love a good helper, col. The only issue I run into is that many people share and access my dashboards at once. They always fight over the dropdown because if one person filters, it filters for all persons. Would you happen to know of a solution to solve this? Right now, I have to rework my dashboards to function around slicers.

[@michaelrusso2535]

Do you have a video on creating the weekly dashboard?

[@geraldbalingasa1875]

very helpful video always watch your tutorials due to its significant values and very relevant to our work filed. Just some question regarding dropdown selection because on the video it shows only for 1 selection. What if I want the combination result of 2 or more people? hopefully you can make a tutorial about that as well. thank you

[@ernst-joergoberhoessel8363]

Thanks, cool trick using the drop down filter column. What happens if new sales data is ingested, typically from an external source?

[@hustlesheets]

Do you mean from separate spreadsheets?

[@ernst-joergoberhoessel8363]

@@hustlesheets The sales data in the dashboard sheet are normally not the original data, just a copy. The data origins typically from your CRM. Either you have to download a .csv file or can use a web API via Google Script to access this data automatically. Think of it as a raw data file / sheet with more details / columns per line. Then your Dashboard sheet would link to the raw data to get the needed information per line.

[@shubhrantshukla4139]

Really loved your videos. I have question What to do if you are dealing with weekly data.? What to write in query formula that has filter by week

[@divyagupta7958]

Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: DATE2021. sheet showing this error.Please Help

[@ehurley8963]

Great video, extremely helpful! Is there a work around to get the data to populate if the date fields are blank? Thanks for all the great content!

[@paulaandreacondequinones1438]

this is amazing!!! Quick question. What if I want to instead of showing the sales amount, I want to see the "LEAD SOURCE" information. What should I write in the query after "select"?

[@romeoloonjr4712]

Do you have the query with pivot for dashboard. Like instead of Sales , let's talk about behaviors of the agents. It could be a repetitive behavior per workweek or time range

[@criskopalan]

Thank you for your videos. I have problem in Google excel dashboard. While using Freeze function, i am unable to view chart, how to solve it

[@madhavin3390]

Hello Sir, that query Foemula not Working to my sheet.....what ever You said as it is i appied but it showing .......#VALUE......, Could you please clear my doubt sir

[@Ashtrey74]

Amazing video as usual. You are using dates to sort currently. Is there a way to sort using square footage instead of the month?

[@GabrielCosta-wn1wt]

Thank you for this video. I'm facing the following issue: now (2024), Google has included a "edit" button on the dropdown menu, once you set up data validation. When you share the report with end-users, they need to have edit permissions in order to be able to select a value from the dropdown, as a view-only permission will not allow it. However, even if you protect all remaining cells and items, once they open the dropdown menu, the "edit" button is available for them. So you can set up validation from a range and even protect that range, but the end user is still able to just modify or even delete the validation, screwing everything up. Is there any way to fix this?

[@noemisanchez7521]

My filter columns are working as they should but my dropdown filters on the dashboard are empty whether filled or not. This happened when I changed (Ex.) H = """&E2&""" to Q = TRUE. Any idea what could have gone wrong?

[@0xJericD_]

Same problem

[@hendricklin6438]

Love your videos! By any chance that we can know how to do if we would like to have multiple choices at one time? Like the sales results of two sales members.

[@alex.username]

You can use slicers.

[@LanieLuvsBeauty]

I'm following exactly what your doing when adding the month column and formula. For some reason, when I hit enter it disappears. Not sure what is going on.

[@MohamedKhalifa-uj8ye]

Hello how to use countif formula instead of SUM in the same Query formula you used in the video. Thanks

[@catherinenamoc8126]

Hello, Is there a video on how to do the google forms response? thanks

[@NikiSadr]

DUDE this is the best - YOU are the best. THANK YOU!!

[@loganoakley5267]

This is so helpful for what I am making right now. Question for you: Is it possible to do this referencing multiple sheets? My company deals with over 2000 entries in any given year, and I have our stages (your status field) broken into multiple sheets in the book. I set up an automatic move so that when a different stage is selected from the data validation, it moves the row to the appropriate sheet. We don't have a master sheet (what I would refer to your Sales Data sheet) that has every entry on it because of clarity. I have found through trial and error that =query can only have 3 arguments, and we have 6 different stages (sheets). Any help would be appreciated. I attempted to go to your website to look into hiring you, but I just got an error: page not found.

[@naominyarko5288]

Hi Logan, This is my exact question! were you able to figure it out?

[@_NineEleven_]

How to make a query in dropdown menu with hyperlinks shown? Thank you

[@gaspargevorgyan5341]

Hey, Thank you for this quality of content. Very Interesting, easy to understand, good explained.

[@Toulimen283]

Nice job. If you were to filter by quarter would you need a column for quarters in the sales tab?

[@christine_notchristina]

most likely. helper columns, i believe, are the go-to for data report needs whose source data doesnt include it in the original data

[@oliviaamyette6474]

Hi! Awesome video. Is there a way to set recurring reminders to a user's email on sheets that terminate when an action is taken?

[@oliviaamyette6474]

@Nwachukwu Ujubuonu I am! Is there a way to get that done for free? I appreciate the help!

[@alphabeta01]

Hi, great video again. Wanted to seek your advise, I use Google Sheets for equity and options. I keep all historical data within the same sheet and have some dashboards around. Is this advisable? Also, how do I protect all historical data say older than 6 months from any accidental edits/replacement/deletion etc. Protect does not work great unless I am doing something wrong. Thanks much in advance.

[@chasitydaniels4808]

Can / will you show how to Build a google form that the info is inputed to & then results in theses sheets & dashboards? This way my team can each fill in their FORM with their activities BUT filters into one sheet so I have a team dashboard

[@jamielynabundo7177]

How about if you have already the month alone? there's no date and year? what's the formula look like?

[@Teti_90]

Wow, you’re awesome! Thank you so much for all the information!

[@BarakAlmog]

Appreciate your efforts. Great video. Thanks so much!

[@varuncd1096]

What if I want to create a chart that could change according to the date range. I mean I can do the same process as above. It won't tell me about any specific date range I want to see. Please help.

[@pratikshamangrulkar70]

Hey, will you please tell me how I can create a currency dropdown with all currencies included? By selecting one, it's going to change all over the page.

[@isbkurunegala]

Hi hustle, Can you teach us how to hide the menu bar formula bar to viewers... I mean without any controllers only rows and columns...

[@mahsekerpolat3817]

Is there a chance to combine the benefits of SortN and the Query function you can think of? I have a dataset full of measure results and based on selection the Plan is to only max 10 results

[@BasitAli-mx6cm]

if i want to prepare 2 drop down on google sheet one by name and one by month on my dashboard how i can prepare it ? while i had prepared working sheet by employee name & in these sheet every employee performance on month base. so i want on my dashboard that if i select name by month i will get my result so please suggest me some solution

[@AshutoshKumar-vo6xv]

What to do if I want to select multiple values in drop down, it is just for choosing one value? I want all my charts changing for multiple values selected, Slicer is doing it bt it is not updating automatically

[@Youtoobh]

Can anybody tell how we can do the same thing with an Employee or client Name?

[@kshitijagrawal7653]

Hi, is there any possible way to connect multiple google sheets to a single google form and support direct data entry?

[@talebbagazi1435]

Try use the =filter function

[@talebbagazi1435]

@Nwachukwu Ujubuonu yes you are right we need to use the =importrange and would be be able to have as many gsheet as you want in a one master sheet

[@talebbagazi1435]

@Nwachukwu Ujubuonu write a script inside the =importrange or do you mean something like vba?

[@talebbagazi1435]

@Nwachukwu Ujubuonu awesome will study it ASAP

[@peratambayph]

I need help for my excel report sheet😢 can you help me with the formula sir,

[@cyril_leonel4713]

Is there a way to do this in excel instead of google sheet ?

[@ibrhmmcht3907]

6:26 how do you ad new row in cell when i was pressing enter it skip my current cell

[@hustlesheets]

Option + Enter for Mac :) Otherwise try Alt + Enter or Shift + Enter

[@ibrhmmcht3907]

Okay thanks for it. It is very useful your are great @@hustlesheets

[@ankitmishra-zw9vc]

Hey, bruh the video is awesome! Just wanted to ask do you have any plans to make online marketing/ performance marketing dashboards too ? 1. Google Analytics Dashboard 2. FB Ads Dashboard 3. Google Ads. And a kind of master sheet.

[@hustlesheets]

Yes I do!

[@charaf98]

You're amazing bro, I love your videos. I like playing with data in my business and have control over it. I manage my team on google sheets but with more data Google sheets become slow, could you suggest an alternative solution, I heard about big query. Thank you

[@trixily]

I would love to see something like this focusing on Inventory Tracking

[@g.speranzini]

Incredible content. Thank you

[@analyzethislexi]

Does anyone know how to get this dataset?

[@asjadahmed1636]

Hey! Your videos were a great help to me. But while executing the dashboard in sheets, The filter worked only for two boards, as instructed I tried copy pasting And R >= Date..... for others it shows #Value Error Unable to parse query string for Function Query parameter 2: No_Column:R. Could you please help. Thank you!

[@buildwiseapp]

Brilliant, thanks so much!

[@cheerbear9059]

My Query formula to bring over to the dash board is return my months and year ascending. Can you tell me how to switch it?

[@hustlesheets]

just add an "ORDER BY X DESC" at the end of the query (swap out X for whichever column your Months and Year column is) You can even do "ORDER BY X,Y DESC" to sort by two columns or ORDER BY X DESC Y ASC

[@MaryEStewart]

New sub. Is there any way to "lock" the dashboards from further editing as you set them up for clients? Also, need a consult to get a few questions answered. 🤔

[@amolapr]

you can protect the sheet and just choose which specific cells new users wont have access too. For the dashboard i created, i protected all the the cells except the ones for the dropdown menu so they can till choose which data they want to look at but cant edit anything else

[@MaryEStewart]

@@amolapr Awesome, thanks!

[@vignesh.balasubramanian]

"Crunch those numbers again, just crunch." - Michael Scott

[@bseprotectionpreventionpre2009]

Hey man, awesome video, I have an issue that I can't seem to solve where linking the Sales Person to the Dashboard filter i get the VALUE ERROR of NO COLUMN:W? is it a bug?

[@raghadmurshed7603]

Check the range of query formula (the first parameter)

[@yowcheechoykpm-guru1977]

hi, how do we prevent the charts from being edit by the other editors?

[@flori480]

You only give others “view” not editor privileges” you can also “protect google sheets”

[@jesualdobraga9963]

Amaz, thank you so very much

[@worrywots]

Love the walk throughs ... alas i keep getting a FALSE and not TRIUE when I tried to replicate this in my own sheet. This was the formula i recreated. =IF('TBC_Master Dashboard'!$X$15 = "",IF('TBC_Master Dashboard'!$X$15 =B2, TRUE,"" )). When i hover in the formula, the nested formula says TRUE, but the result says FALSE. I can't see where I went wrong.

[@PhuongPham-ox6se]

Thank you very much

[@fatimaaaarrrhhhhhr]

gracias!

[@selvarajandiappa3143]

Hi.. Just to check.. can you help us set up..we give you the fields and requirements..we will be happy to pay you for the services

[@NurSobahx]

Thank you

[@romeoloonjr4712]

Thanks bro

[@HichamBELABED]

09:36

[@samadders9]

date """&text(E2,"yyyy-MM-dd")&"""