Managing staff timetables- pulling data from multiple sheets and collating

[Evie01]

I have a complex staffing sheet. We have several locations, and at each location we have several staff members working on time defined contracts. ie in Asia from 20 Jan to 05 Feb, Bob, Sue, Sally and John are in the team.

How we have sheets set up now works very well for us to see a good overview of where we have gaps in the staffing, and how staff flow across contracts - as they may do six or seven consecutive ones. However, staff might also move from location to location, some will work for a couple of months and then have a big gap etc and we don't have a good way of keeping an overview of how much each staff member is working. So if Bob forgets his list of contracts and needs a reminder, I have to manually go to the sheet and search.

I've just mocked up with fake names an abbreviated version of our sheet and any pointers would be gratefully received! I've put in the master sheet that I'd like to create, but I did all of that by copy pasting and what I'd love is something that pulls automatically, so I can filter and sort and extract further, I just don't know where to start

[protonLeah]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Evie01]

I've edited the subject line and hope this is now clearer?

[JeteMc]

As this has been here a few days I thought I might offer the following:
1. Type the information into the table on the staff master sheet. (it's got to be typed in once anyway)
2. Utilize formulas to fill in the continent sheets. (modeled for Europe Contracts 1 & 2 and Asia Contract 1)
3. Now you only have to use the filter button in cell A2 on the staff master sheet to find the information you want!
The formula for Start location, End location, Start and End is: =INDEX('Staff master'!$L$3:$O$5,MATCH(B$4,'Staff master'!$K$3:$K$5,0),MATCH($A2,'Staff master'!$L$2:$O$2,0))
The formula for the names is:

Please Login or Register  to view this content.

Additionally there is a small range of cells in columns K:O on the staff master sheet which is filled in using:
For Contract: =IFERROR(INDEX(tblStaff[Contract],MATCH(0,INDEX(COUNTIF(K$2:K2,tblStaff[Contract]),,),)),"")
For start and end: =IF($K3="","",INDEX(tblStaff[From],MATCH($K3,tblStaff[[Contract]:[Contract]],0)))
More automation is possible (i.e. data validation drop downs to Contract on continent sheets), however I don't know if any of this is going to be acceptable so I didn't want to spend too much time.
Let us know if you have any questions.