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.
Note: The technique does NOT 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.
GetPivotData Formula
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
__________________
Hello Debra,
The link for the sample file from this blog post takes me to a “Not Found” error page.
This is the url:
http://blog.contextures.com/archives/2012/11/29/choose-report-dates-with-excel-scroll-bar/www.contextures.com/excelscrollbarselectdate.html
However, if I open the page http://www.contextures.com/excelscrollbarselectdate.html, the link to the sample file works fine 🙂
Perhaps you could update the link on the blog post page ? Thank you.
Khushnood viccaji
Mumbai, India
@Khushnood, thank you for letting me know about the broken link — it’s fixed now.
i am working “excel data entry form” (parts order data entry) its working well….but i want bit changing, i want auto generate Order ID.
i put first Order ID manually and add data, when i add data and press clear button and fill form without order ID an its work automatically check database look last order ID and plus one and use it as order ID at the time of second entry.
is it possible? if yes please guide me and tell me which formula work as i want.
@Mike
the formula in your OrderID field should be something like this
=max(orderid)+1
HTH
OSilva