Creating a Scroll bar to filter a Pivot Table (By Time) Excel 2010.

vhookup

New Member
Joined
Mar 6, 2018
Messages
19
I have the below Pivot table (it goes through to zone 8), The data in the columns is number of people in an area at a given time. I would like to add a scroll bar connected to the pivot to filter this table (a total of 17 positions) i.e. position 1 showing 06:30 and position 2 showing 07:30 etc. I have Slicers set up to filter different dates (of which there are many). The end result is going to be a conditionally formatted map where you can scroll through the timeline (scroll bar) to view population of certain zones - (this bit i have figured out, i just need help to filter the time so I can see one row in my Pivot table that is controlled by the scrollbar - as you go along the bar horizontally the time increases and vice versa)

I have tried recording macros and adapting other similar projects in VBA but had no luck, I'm new to the VBA side of things (not used anything like that since before the turn of the century) I have also seen there is a timeline function in later versions but I'm only in 2010.[TABLE="width: 500"]
<tbody>[TR]
[TD]Time[/TD]
[TD]Zone 1[/TD]
[TD]Zone 2[/TD]
[TD]Zone 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06:30:00[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07:30:00[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08:30:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09:30:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:30:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11:30:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12:30:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13:30:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14:30:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15:30:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16:30:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17:30:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18:30:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19:30:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20:30:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21:30:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22:15:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for your help.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
The timeline feature won't get to hours. Finest granularity is "Day" of month.
You will need some VBA for an Event so that when you change your desired Control Element, it will apply to your Pivot Table.
Start at the Developer's Ribbon and work through the Controls. Probably a slider control with a range of 17 values. Feed that to cell that is monitored by ChangeEvent to get the update and apply to the Pivot Table.

However, there are huge benefits to being on 2016 and Office365. The Slicers feature does seem like a great fit to your goal.
 
Upvote 0
You will need some VBA for an Event so that when you change your desired Control Element, it will apply to your Pivot Table.
Start at the Developer's Ribbon and work through the Controls. Probably a slider control with a range of 17 values. Feed that to cell that is monitored by ChangeEvent to get the update and apply to the Pivot Table.

However, there are huge benefits to being on 2016 and Office365. The Slicers feature does seem like a great fit to your goal.

Unfortunately I'm stuck with this version of Excel until the company decided to update it. So I'm kinda pleased the timeline function isn't the answer.

I've got the scroll bar set with the parameters. Does a slider allow you to input time values as a range?

I'm off to do some research on that "change event" you mentioned. Thanks for the input. I'll report back.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,193
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top