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: =INDEX($B$4:$G$14,ROW()-3,MATCH($H$3,$B$3:$G$3,0)) 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.
How do your Excel skills stack up?
Test NowNext up:
- Adding videos to your website
- The problem with accepting constructive feedback
- Get a Better View – Through Office Lens
- PowerShell Profiles – Have it Your Way
- Transparent Images in PowerPoint 2013
- Configuring Lync Room System
- Rugby in the workplace
- InDesign – Data Import Feature
- Streaming Xbox One to Windows 10
- 3 Simple Pie Chart Tricks
Previously
- Service Design in ITIL®
- Slugging it out, and the need to bleed
- Remember Me? In one way I hope not!
- Configuring an internet facing deployment for Microsoft CRM Server
- Custom number formats in Excel
- A big flop that broke the rules and broke the records
- Convert a Column to a Link-to-Item in SharePoint
- Office Politics
- Text to Columns – First Space Only
- Creating an awesome In-Memory Database