• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Scrollbar for multi-week period selection|Power Pivot|Excel Forum|My Online Training Hub

You are here: Home / Scrollbar for multi-week period selection|Power Pivot|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower PivotScrollbar for multi-week period sel…
sp_PrintTopic sp_TopicIcon
Scrollbar for multi-week period selection
Avatar
Maarten de Keijzer
Member
Members

Power Pivot
Level 0
Forum Posts: 35
Member Since:
September 2, 2020
sp_UserOfflineSmall Offline
1
March 18, 2021 - 9:20 pm
sp_Permalink sp_Print

Hi,

I'm trying to use a combination of a few example Excel sheets to build our own Project Management Dashboard,
where our data is a combination of 3 input tables.

In yours I like the scrollbar to be able to scroll through the weeks.
However, we have to present weeks in columns, not the days.

I have seen solutions like in the sheets 'rolling periods' or 'slicer-multi-years' but they process the current date,
and do not act on user-decided start and end range.

I have been experimenting with an extra column in the Date-table that calaculates 'selected' with respect to the (Scrollbar) defined range,
but the calculation is only done on 'refresh', not instantly.

Is there any possibility to make it work?

 

Regards,

Maarten

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4518
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
March 19, 2021 - 12:49 pm
sp_Permalink sp_Print

Hi Maarten,

Are you able to share your file showing what you've done so far and explain in relation to the file how you'd like it to work?

Mynda

Avatar
Maarten de Keijzer
Member
Members

Power Pivot
Level 0
Forum Posts: 35
Member Since:
September 2, 2020
sp_UserOfflineSmall Offline
3
March 19, 2021 - 7:58 pm
sp_Permalink sp_Print

Hi Mynda,

I'm happy to share my current Test-file; see attachment.

In tab Data I have defined cells to control the selected range:
StartWeek, EndWeek and a Range (Bereik; allowing for 16 columns).
In the same tab I created a Date-table (WekenInput) with a formula that makes a number of weeks 'selected',
based on the defined Start- and EndWeek. The table is copied to the Data model.

The tabs Cap, ProjInzet and Real are in fact copies, just showing different parts of the data,
and all three now have a slider to be able to select the range.
Later on these three have to be combined into one overview (probably I need to merge the tables for that),
and then showing Available time, Planned work and Realised Work.

When I now move the slider I have to refresh the query Weken to update the values in the Data model,
in order to get the correct selection of weeks within the columns.

I hope there is a method to create a more responsive way of working.

 Maarten

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4518
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
March 21, 2021 - 12:12 pm
sp_Permalink sp_Print

Hi Maarten,

Thanks for sharing your file. The only way to make this more responsive is to move the filtering to Power Pivot rather than the data source. e.g. you could use a Slicer instead of a scroll bar that allows the user to select the periods they want to display. This means all data will be in your model ready for filtering on, rather than excluding data from being loaded as it currently is.

Mynda

Avatar
Maarten de Keijzer
Member
Members

Power Pivot
Level 0
Forum Posts: 35
Member Since:
September 2, 2020
sp_UserOfflineSmall Offline
5
March 22, 2021 - 6:33 pm
sp_Permalink sp_Print

Hi Mynda,

Thanks for looking into it. I have tried to use a slicer for the year-week periods, but it keeps a little bit more complex.

Knipsel.JPGImage Enlarger

For the end-user it is not as easy as ticking the scrollbar to move on a week, he has to select and de-select.
As the data contains weeks for two calendar years the number of periods within the slicer is rather big.
The administration system (source for the data) sees the start of this year as week 53. That week appears at the end of the slicer.
So, I hoped to be able to produce a better interface.
Is there any possibility to control the slicer content by a scrollbar, or so?

Maarten

sp_PlupAttachments Attachments
  • sp_PlupImage Knipsel.JPG (60 KB)
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4518
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
March 22, 2021 - 6:57 pm
sp_Permalink sp_Print

Hi Maarten,

Have you tried the Timeline Slicer? 

Mynda

Avatar
Maarten de Keijzer
Member
Members

Power Pivot
Level 0
Forum Posts: 35
Member Since:
September 2, 2020
sp_UserOfflineSmall Offline
7
March 22, 2021 - 10:34 pm
sp_Permalink sp_Print

Hi Mynda,

In fact I rather rejected the Timeline slicer, because you mentioned a few disadvantages of it in one of your example sheets.

But, if there's no other solution, we'll have to use that as a compromis.

Thanks,

Maarten

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4518
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
March 23, 2021 - 9:07 am
sp_Permalink sp_Print

The only other option is to write some VBA code to handle the filtering, but this wouldn't be as smooth/quick as the timeline slicer.

sp_AnswersTopicAnswer
Answers Post
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Mohamed Jelle Hussein
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Aminu Sule
Samuel Ramirez
Abdifatah Osman
mohad borhom
Abayomi Adedeji
Ganesh MVS
Gilbert Lemek
Ashleigh Farquharson
Jayz Luu
Fred Smith
Forum Stats:
Groups: 3
Forums: 24
Topics: 6364
Posts: 27828

 

Member Stats:
Guest Posters: 49
Members: 32346
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.