Hey there stranger!

Sign up to get access.

Switch Months Menu - Design a Better Dashboard Part 4

About this Tutorial

Create a dropdown that changes the months displayed in a dashboard.

Featured Formulas

Video Transcript

 Hey, welcome to part four, where we're gonna make this dashboard into an interactive dashboard. Basically, I've already done this. I'm gonna show you how to do it, but it's a dropdown menu that says, pick a month, and now, instead of seeing just the last month, we can pick any month. And the numbers change.

Even this gauge chart changes based on what month it is we select here. So I'll show you how to do this. We're using index match, one of my favorite formula combinations. But let me get rid of one thing first and then we'll start. So I got rid of everything I added, and then I'm gonna walk through how to add it.

So we're gonna add a dropdown up here, and then we're also going to fix some formulas here to make it US usable. And then we're also gonna fix how this gauge chart works. Which cells is it looking at? Just to make sure that it operates based on this dropdown menu. So first, add a couple.

A month. Can say any label you want. Here, I'm gonna add data validation. This how you make a dropdown menu list from a range. And the range I'm gonna pick is actually gonna be on this work. It's going to be all across here. Let me add a few more month, few more just in case we add just in case we add months.

Say, okay, now we go back, let's hide this chart. This is what we're going to, let me just hide that way. Let's go back dashboard. And now we have a dropdown menu, right? And now we have all our months, but nothing happens, right? So let me color this blue so I know this is what I'm gonna be changing.

And in order to change this, the formula here in B seven, right now it's saying index count, count all of the things in row three, whatever is the last one. Go to that in row three A. This count needs to, it needs to be based on what is in that dropdown menu. So use match for that. So we take this out, we go match.

That's weird. Match. If we can spell it correctly, match what we're matching is a search key is going, let me close this gonna be D two. We're gonna search. Now. This is the only weird part. We are going to be searching across the work sheet two to two. So we're finding how far along is that name, right?

We're gonna match whatever is in the dropdown menu to here, figure out what that column is. And that's our number. So we're gonna say, and we're gonna say common zero, because we want an exact. So now this index match function is going, Hey, look at work. A three row, look at the third row, go to the first row.

So this first argument or second argument here is row. So go to the first row and the column is going to be a number, but it's based on whatever we, wherever we find this. So it's. So 1, 2, 3. Third column, go there and number. And if we look at the dashboard now, it's April, and that is in the third. So April, may.

And now this number changes automatically based on whatever we put there. And so all we're gonna do is use this match now in play of this count all. Okay, so now we have March, April. And even this changes. We don't have to do anything about that. Okay? And all we have to do is fix this count all again, just go based off of this D two and it all works together.

We're just last couple here. We're gonna take out this count all. And put in a match to this. It's just searching for this. So now all of these numbers change based on what we have there, April and May. Now the next step is to fix this gauge chart. So the gauge chart, we had to go in here and edit the chart and figure out that he is.

These columns here, right? So what to do to get one number can't really do match here. What I'm gonna do is I'm gonna add a column here, and I'm gonna say, I'm gonna use match here. So I'm gonna say equals index.

And I'm gonna say index is going to be. 13th row, which is this index, right? The row is one, and the column again is match. Same thing match B2 in this range, it's gonna be match

in this range. Zero.

And so all we have to do now is we wanna hold this, use a dollar sign to hold it so it doesn't change. Do dollar sign D, dollar sign two. And now should be able to just copy and paste that down in the row, down the column, and we can check, we can say, okay, March. Should be 13, 11. Oh, we have to change where this is going.

Oh, sorry. One thing. So we gotta edit and point to that B column. Instead of the value being D, we're going 0.2 B. And so the change 2, 4 41, 2, 4. Then April 1, 3 11, that change 1 3 11 1 3 11. It all works. And we can see this change all across the months. Make this a little bit bigger. Maybe that's you see that age.

Can see April 13, 11. March 24 11. That matches March over here 24 11. And we made a, based on the month we pick here and that's it. We're done. Thanks for watching. Bye.