Feb 15, 2016
By adding a scrollbar control to a Microsoft Excel
sheet you can bind it to a range of values. As you scroll, the values in that range will be returned into a cell. You can now use this dynamically generated value to do something with it. Let’s take an example;
Here’s a table of annual sales per model numbers. We would like to create a dynamic chart to show the trend of sales for each year as we scroll through the years.
To do this, we need a scrollbar on the sheet first. From the Developers tab drop down Insert and select Scrollbar.
Draw it in an appropriate place on your sheet. Then from the Developers tab select Properties
. Set the Min and Max values
and the Incremental Change. In our case we set them to 2010, 2015 and 1. In the Cell Link nominate a cell where you want the values to be returned in. In our example we set it to $H$3.
As we use the scroll bar now, the values in H3 will take on any of the values 2010, 2011, …, 2015.
We can now get this value and make use of it.
As an example, we are going to Chart the sales for each year. This chart should be automatically updated as we scroll the scrollbar. So what we do is that we chart column H which should be a replica of the year column that appears in cell H3.
So assuming I have year 2010 in H3, I type this formula in H4:
And Autofill it down to get a copy of the column 2010;
As we now scroll the bar, the value in H3 changes to different years and so do the data in column H. We will now select columns Model No: and the last column (H) and insert a Line Chart.
As we scroll using the Scrollbar, my chart will be updated automatically because the column H is updated.
The development of the formula is up to you. Here, I just wanted to demonstrate that the value returned by the scrollbar is usually being used in one way or another.