Creating job schedule for multiple employees from single job list

Good afternoon all!

I'm trying to create a schedule to keep track of jobs booked in for multiple employees.
Ideally, we'd like to have a running list of jobs (job scheduler tab) where we enter;
- date job is booked in to be done
- start time
- finish time
- staff member who is to do it
- job details - basic info: name or address, details, that sort of thing

We then would like that info to auto fill in the daily schedule under the staff members name so we can easily see who is where at any given time.

I've tried VLOOKUP and CHOOSE functions as well as anything else i can think of by i cant get the information to split how it is supposed too.

Any help would be GREATLY appreciated

You could use a helper column in the Job Scheduler sheet which identifies the member of staff and the start time, and then in the Daily Schedule sheet you could re-build that reference and use it in an INDEX/MATCH formula to get the job description. For example, use this formula in cell L3 of the Job Scheduler sheet:

=IF(E3=DateVal,F3&"_"&TEXT(G3,"hhmm"),"")

and copy this down as far as you need it. Then in I4 of the Daily Schedule sheet you can use this formula:

=IFERROR(INDEX('Job Scheduler'!$I:$I,MATCH(I$3&"_"&TEXT(H4,"hhmm"),'Job Scheduler'!$L:$L,0)),"")

and copy this down. You can also copy it into L4 and so on for the other members of staff. Change the date to see the job list change automatically.

This only shows the start time for each job, but if you wanted to show all times that a job is being worked on then you could expand the table of jobs from column L in the Scheduler sheet.

Hope this helps.

Pete

Hi Pete,

Thank you so much for that!
I'm confused how to expand the table to show the whole time the job is being worked though?
I've uploaded the latest spreadsheet... It's so close!

The attached file shows how you can do this. I've added an extra column to your table (seq) which identifies only those records which relate to the date selected, as it is only these that need to be expanded. This formula is used in K3:

=IF(E3=DateVal,MAX(K$2:K2)+1,"-")

In columns M and N (coloured blue) I have formulae which relate to the time increments for those records, as follows:

M3: =IFERROR(MAX(1,ROUND((INDEX(H:H,MATCH(ROWS($1:1),K:K,0))-INDEX(G:G,MATCH(ROWS($1:1),K:K,0)))/Increment,0)),"-")

N3: =IF(OR(M3="-",M3=""),"",M3+N2)

M3 works out the number of increments which each record covers, and returns 1 if there is no Finish Time (as in row 6), and column N just accumulates these increments. I've copied these formulae down to row 50, which should be sufficient to include all the day's job list. Note that there is a zero in cell N2, which is important for the formulae which follow.

The formula in O3 is the one which controls the "expansion" of the records:

=IF(ROWS($1:1)>MAX($N:$N),"-",MATCH(MATCH(ROWS($1:1)-1,$N$2:$N$50),K:K,0))

and it returns the (absolute) row number where the record exists by looking at the cumulative count of increments in column N as well as the position of the record in column K. The formula in cell P3 then picks out the time for each record and increments this time for each repetition of the record:

=IF(OR(O3="-",O3=""),"",INDEX(G:G,O3)+(COUNTIF(O$3:O3,O3)-1)*Increment)

Column Q returns the name and time reference that I was suggesting in the earlier post, with this formula in Q3:

=IF(P3="","",INDEX(F:F,O3)&"_"&TEXT(P3,"hhmm"))

and column R just picks up the appropriate job description:

=IF(P3="","",INDEX(I:I,O3))

I've coloured these cells with a pale yellow and copied them down to row 150 - you can see by looking at the maximum number in column N how many rows you actually need. These columns can be hidden if you wish.

In your Daily Schedule sheet you can then have this formula in I4:

=IFERROR(VLOOKUP(I$3&"_"&TEXT(H4,"hhmm"),'Job Scheduler'!$Q:$R,2,0),"")

and copy this into L4, O4, R4.

Then you can change your date and see the daily job list change automatically.

Hope this helps.

Pete