Choose Report Dates With Excel Scroll Bar

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.

dateslider10b

Get Data From a Pivot Table

The sales data is summarized in a pivot table, by report month, and region.

dateslider06

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.

dateslider15

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

__________________

You may also like...

5 Responses

  1. Khushnood Viccaji says:

    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

  2. @Khushnood, thank you for letting me know about the broken link — it’s fixed now.

  3. Mike says:

    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.

  1. December 4, 2012

    […] Choose Report Dates With Excel Scroll Bar […]

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *