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.
Hi Jonathan,
Please supply your workbook so we don't have to recreate everything.
Thanks
Phil
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
