This week, I've been working on some dashboards, and want to make it easy for people to select a date range for the report.
I experimented with drop down lists and slicers, and finally settled on a good old-fashioned scroll bar. You can click or drag the scroll bar to select an end date, and see three months of sales data, and the total.
The technique doesn't require programming and is fairly easy to set up.
Scroll Bar Select the End Date
The scroll bar on the Summary sheet is linked to a named cell on another sheet, and that number is used in an INDEX / MATCH formula, to calculate the end date.
The date headings have formulas that show the selected end date, and the two prior months.
Get Data From a Pivot Table
The sales data is summarized in a pivot table, by report month, and region.
The summary table uses the GETPIVOTDATA function to pull the correct data, based on the region name and the date.
The IFERROR function returns a zero, if the data isn't found in the pivot table.
Download the Sample File
To download the sample file, and see the written instructions, please visit my Contextures web site: Select Date with Excel Scroll Bar