DAILY ROSTER.xlsxBear with me because I know very little in regards to functions on Excel but am interested in learning and using a function to complete the scheduling tasks we complete on a daily basis. I want to apologize in advance if my terminology is incorrect in describing what I am trying to accomplish, this seems to be the root of my issue in searching for the solution in itself. We are currently using Excel 2010 for both documents. The first thing I posted was a monthly schedule that we use for assigning posts to individuals and this is done well in advance of the time to let the staff know a tentative posting of their schedule. I placed a key on the final worksheet showing the Abbreviations we use for each post. Also on this sheet in column B I placed a formula to count each time people are in specific posts and it seemed to be working well until I discovered that if on worksheet you place a value that goes to wards the count the next month it won't count that cell. Not sure what the issue is.
The next sheet daily roster that by the supervisors on a day to day basis. This is done for 3 shifts a day, 365 days a year. As you can see this is a pretty daunting task. I am looking for the assignments from the monthly schedule to be auto populated into the G column under original postings. There is multiple other cells in this document that never change and would not need for it to auto populate. As you can see there are multiple spots that require more than one person. For example I would like to be able to assign two individuals to Control Center and have Excel check to see if slot one is full to auto fill slot two. Otherwise we could individually assign CC1 and CC2. Currently on the table/calender we do not individually identifseparatelyly. The other wrench that I will throw into this of the is that some spots that we specifically need a female staff member. On the first calender I would like to be able to identify a staff member as a female and when auto populated they would fill the female spot leaving the other. On the other hand females have no restrictionotherer placements in posts and can work any. For example Booking 1 is a female only spot but male or females can work in booking 2-4. The last thing would be that posts like the pods are a single post. If supervisor accidentallyly assigned two people to N-pod I would like the two cells to turn red showing an error. This seems fairly easy and is my next order of research.
Whe I get all this complete I want to set up Lookup to be able to get a specific count for each employee of where they have been assigned during a specific time period.
I placed examples of the sheets that we use. Currently each supervisor uses a separate monthly schedule and all shifts rosters are in the sfiles separaterate worksheets. If that data can be sent between the two files that would be great but if not I could have a monthly schedule and roster in the sfile separatedrate worksheets for each shift. If anyone can provide me some guidance in regards to functions that will complete these tasks but also where my error is with computing the count on the monthly roster would be greatly appreciated.MONTHLY SCHEDULE.xlsxAttachment 348707
Bookmarks