So far, we've had a look at how we can create a great dashboard with high visual impact. In the next step, though, we want to look at how we can create interactive elements to make it more user friendly. So, for example, our users want to be able to see the metrics for different months. At the moment we're just showing them the metrics for July. So we'd now like to add an option for them to choose the correct month from the drop down list. There are lots of different ways you can add interactive elements. You could use slices, data validation, or form controls. You may have noticed we have actually avoided pivot tables in our dashboard. And while that might seem an obvious thing to include, the problem with pivot tables is they have to be refreshed, which means either using VBA, which we want to avoid, or running the risk of not always showing the latest values, which again, would be a problem. So we're going to focus on the two other options, data validation, and form controls. For this one, we could use a form control, but form controls are a lot harder to format. So we're actually going to stick with data validation. Now we've merged these two cells to make life a little easier, because September's not going to fit in a single cell, and we're going to add our data validation. So coming to our data tab, we're going to click data validation, and we want to this to be a list. And we have created a named range, which refers to the month names calculated in our calc sheet. So we're just going to press our F3, and the one we're looking for is month names. Then say OK, and if you come back to the cell, you should now see a drop down list has been created, and we can go and change it. But at the moment, this is not affecting our data. So we now need to go into our calc sheet and get this working dynamically. Now these values that you see here and the values in the service level are all coming from this row. At the moment, these values have just been copy pasted from above, so they're not actually achieving anything. We're now going to actually change this to be dynamic. So our first thing is our month name we're going to say equals, come back to our dash, click on the selected month, and click enter. To actually get the correct month number, we're going to use a simple match function. So I'm going to match the selected month name into my month names, And this is an exact match. Now what that's returned is the first row, and I can now use that number to retrieve all the other values in the appropriate row. So here I'm going to use my index function. My array is going to be this little column over here. And my row number is going to be C40. And of course, I need to lock them in place, so I'm just going to press F4 three times to get the column locked, and then close my brackets. That has retrieved the correct year, and I can now just copy this across. Now, let's check that this is actually working. So back in our dashboard, we're going to come to our drop down list, and we're going to choose August. And now, we're seeing a little bit of movement. And we can now see that, actually, service definitely did improve between July and September. And October, it was still good. And then, November, as we feared, it started to drop off. So there was our first interactive element. The other one we're going to look at is how you can use a scrollable table to fit a lot of information in a small space, which is something you may need to do with your dashboards. And once again, we're going to need to set up our data. So coming back to our calcs, We actually have the data set up for us already, but I'm going to just explain what we've done here. And actually I'll maybe do those formulas from the beginning. Now what we want to be able to look up is the amount of time each of our agents are actually spending on calls with clients. This information has been calculated here for the last 28 days. But you can see it's a lot of information, and we won't be able to fit all of that on the dash. So what we're going to do is use an index function to look up the seven sets of values that correspond with these numbers across the top. So it's a nice simple index. Our array we have named. It's our DB TableData. Our row is going to be whatever the row number is here. And just remember to up the column, and our column is going to be whatever the number is here. And having done that, we can copy across. And when we copy it down, we must just remember to fill without formatting so we don't get the date format. So what that's doing is looking up the first seven days, which is a start. But obviously, we wanted to be able to look up maybe from two to eight, or three to nine. So the way we're going to do that, just going to leave that as one. But then we're going to create a calculation that says, this is whatever this value is, plus one. And then we're going to copy that across. And what that means is, if we actually change this number to two, we're now getting our data effectively scrolling across. Put this back to one, for now. So now, all we need in our dashboard is a control that will change this number. And we have the perfect control, it's called a scroll control. Let's come back to our dashboard, get the data in, and then we'll look at adding our control. So, we're going to start by clicking, In B27, and I'm going to type equals, come to my calcs, and click on the first value we want to pull through, which is actually an empty cell, but that's fine, and click enter. We're now going to hop back onto that cell, and we're going to copy it across. But then again, don't forget to say fill without formatting. And then we're going to scroll down, And that worked fine. So now, we'll just come and fix these dates. So those should be a date format. And if we want to add a little formatting, we can do that, so I'm just going to make the field color there black. And you could go add some conditional formatting as well. But now we need that control. So for this, we're now going to come to our developer tab, and we're going to come to our insert, and we're going to choose a form control. There are quite a few to choose from, and we've had a look at some of these. But the one we want now is the scroll bar. Click on your scroll bar, and then you can draw it in, either horizontal or vertical. And we want a horizontal one just under our data. But it's not going to do very much until we edit the properties. So, with it still selected, come up to your ribbon and click properties. And we have to specify a current value, so we're going to make that one. Our minimum value is going to be one. And our maximum value is going to be 28, because we've only got 28 columns. Our incremental change is going to go up one day at a time, but our page change is going to be seven days. And then most important, our cell link. This is the cell that's going to change when we adjust the control. And, that's going to be this cell back in our calc sheet. So I'm going to click on that one. And now, when we change that, we'll see the rest of the data updates. So let's have a look. Okay, and let's click away from the scroll for a moment, and then when you come back, if you drag the scroll bar, you will see it works a treat. So, that was another way of adding interactive content. Using a combination of simple calculations and form controls, we've just created a scrollable table. While we have had a look at some of the exciting things you can add to the dashboard, keep in mind at all times that while you need something that's attractive and has visual impact, the most important thing is that it communicates the metrics that the business needs to see. Be creative and have fun.
