How to choose different months in an Excel report

I was asked how you add a slider to a report to choose the data shown for different months.

Monthly report showing the slider control to choose the month

This involves two excel devices:

1. A slider from Forms Control and
2. VLOOKUP function

Forms Control You may need to get the Developer Ribbon or Forms Toolbar turned on (see note below). Insert the slider onto your report. Right click on the slider and edit to reference the slider to a cell for the month value, and choose a minimum of 1 and maximum of 12 and increments of 1. If you have multiple sheets you can link the sliders to the same cell to update all pages

Report Table
Design the layout of your report and then use VLOOKUP to find the data from your datamine. I would suggest you label your data with months across the top and have the same field names on the left of your data and report. An example of the formula will look like this =VLOOKUP($B14,B5:N16,$C$2+1,0) – this looks up B14 (e.g. the name of the town in the above example) in the left column of the table B5 to N16, matches and returns the data from the month column $C$2+1 (offset by one column in the table) in the same row.

You can utilise the same basic logic for graphs and text references to return the name of the month etc, and also combine with the OFFSET function to make dynamic ranges to take your reports to a higher level.

If you have lots of data i.e. multiple years use CONCATENATE to combine the look up and logically named ranges for the data e.g FY10B and FY10A etc.

Follow good practice and always divide your spreadsheet into 3 parts:

a) The reports – Visual only
b) Calculation sheets – where the math or lookups are done
c) Data only sheets – where you enter or cut and paste the data in

Note re Form Controls.
– In Excel 2003 and earlier, these were available from the Forms toolbar.
– From Excel 2007, you need to specifically enable the Developer Ribbon.

Advertisements
This entry was posted in Excel Tips and Tricks. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s