• 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

Dynamic Tables based on Time (Schedule Changes)|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Dynamic Tables based on Time (Schedule Changes)|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
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 ForumVBA & MacrosDynamic Tables based on Time (Sched…
sp_PrintTopic sp_TopicIcon
Dynamic Tables based on Time (Schedule Changes)
Avatar
Jonathan Smith

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
March 17, 2020
sp_UserOfflineSmall Offline
1
March 18, 2020 - 11:56 pm
sp_Permalink sp_Print

Hi All,

I am dealing with a case where we are making decisions throughout the day.  Staff are scheduled daily but schedules can change multiple times because of call offs, sickness, etc, and someone else has to be scheduled.

In analysing the data I need to use vlookup (or some equivalent search function) where the table array is date determined.  So for example, on the 18th March I create a table called "Egtnth", on 19th "Ntnth", on 20th "Twnth" and so on.  There is nothing special about the names.  The dates are for the start of a shift and the schedule will have multiple days, e.g.:

Day  Employee  StartShift  EndShift 

18th Emp 1       12:00 pm   7:00 am

18th Emp 2

18th Emp 3

19th Emp 4

20th Emp 5 

and so on.  Is there a way I can write code in VBA to search for the first and last occurrence of 18th and create a table on adjoining data, do so for 19th etc.

I don't know if I am very clear but thank you.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1529
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
March 19, 2020 - 9:05 am
sp_Permalink sp_Print

Hi Jonathan,

Please supply your workbook so we don't have to recreate everything.

Thanks

Phil

Avatar
Jonathan Smith

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
March 17, 2020
sp_UserOfflineSmall Offline
3
March 19, 2020 - 11:33 pm
sp_Permalink sp_Print

File attached

In Sheet Schedule cells G1:N8 I have formulae to do what I want.  The schedule is updated regularly (dates before today are no longer important) so the formulae results will change.

Sometimes the app that generates the schedule puts a lot of blank cells in the data so there is a Macro to remove those blank cells

In Sheet Tracker in columns U:AB I use the table ranges of Schedule sheet to identify the starting shifts of each staff, taking into account the day

Sheet Responses contain the results of a survey they have to respond to daily (screening for COVID19)

So my objective is to dynamically update the Table references for the VLookup formulae.  Names are not absolutely necessary 

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Kathleen Silva
Guest(s) 9
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: 215
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
MARTYN STERRY
Rahim Lakhani
Ngoc Qui Nguyen
Clement Mansfield
Rose .
Bindu Menon
Baruch Zemer
Forum Stats:
Groups: 3
Forums: 24
Topics: 6352
Posts: 27779

 

Member Stats:
Guest Posters: 49
Members: 32319
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.