How to Create Dropdown Filters on Google Sheets Dashboard Using QUERY Formula (ADVANCED TRICK)
Вставка
- Опубліковано 26 лип 2024
- Purchase template here: hustlesheets.samcart.com/prod...
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
Book a consultation: clarity.fm/hustlesheets
Automate data to Google Sheets: supermetrics.com/?idev_id=2372
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
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!
Amazing! What job were you able to get?
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 UA-cam algorithm for recommending it to me.
Thanks for adding videos with filters that make dashboards interactive!😊
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!
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!
Thank you for using the office to make this easier to get through :)
Hey, Thank you for this quality of content. Very Interesting, easy to understand, good explained.
Wow, you’re awesome! Thank you so much for all the information!
Bro, you are the master. This really helped me with my metrics. Big ups!
Appreciate your efforts. Great video. Thanks so much!
DUDE this is the best - YOU are the best. THANK YOU!!
Brilliant, thanks so much!
Incredible content. Thank you
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
Amazing video as usual. You are using dates to sort currently. Is there a way to sort using square footage instead of the month?
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!
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
So clean! I’m impressed
I would love to see something like this focusing on Inventory Tracking
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
Amaz, thank you so very much
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.
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
Thanks!
Thank you
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
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
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
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?
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.
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?
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!
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
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.
Hello how to use countif formula instead of SUM in the same Query formula you used in the video. Thanks
Hello, Is there a video on how to do the google forms response? thanks
gracias!
Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: DATE2021.
sheet showing this error.Please Help
Do you have a video on creating the weekly dashboard?
Thanks bro
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.
You can use slicers.
How about if you have already the month alone? there's no date and year? what's the formula look like?
How to make a query in dropdown menu with hyperlinks shown? Thank you
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
Thanks, cool trick using the drop down filter column.
What happens if new sales data is ingested, typically from an external source?
Do you mean from separate spreadsheets?
@@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.
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
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
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...
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.
Nice job. If you were to filter by quarter would you need a column for quarters in the sales tab?
most likely. helper columns, i believe, are the go-to for data report needs whose source data doesnt include it in the original data
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.
Hi Logan, This is my exact question! were you able to figure it out?
Is there a way to do this in excel instead of google sheet ?
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.
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
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?
@Nwachukwu Ujubuonu I am! Is there a way to get that done for free? I appreciate the help!
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
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.
Yes I do!
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?
Same problem
"Crunch those numbers again, just crunch." - Michael Scott
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?
Check the range of query formula (the first parameter)
Can anybody tell how we can do the same thing with an Employee or client Name?
Does anyone know how to get this dataset?
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. 🤔
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
@@amolapr Awesome, thanks!
Is there a way to enable multiple selection in the filter
Hey I have the same issue did you found any solution yet
Hi, is there any possible way to connect multiple google sheets to a single google form and support direct data entry?
Try use the =filter function
@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
@Nwachukwu Ujubuonu write a script inside the =importrange or do you mean something like vba?
@Nwachukwu Ujubuonu awesome will study it ASAP
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?
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
hi, how do we prevent the charts from being edit by the other editors?
You only give others “view” not editor privileges” you can also “protect google sheets”
I need help for my excel report sheet😢 can you help me with the formula sir,
6:26 how do you ad new row in cell when i was pressing enter it skip my current cell
Option + Enter for Mac :)
Otherwise try Alt + Enter or Shift + Enter
Okay thanks for it. It is very useful your are great
@@hustlesheets
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.
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
09:36
date """&text(E2,"yyyy-MM-dd")&"""
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.