Staff planner - auto populate from data sheet

[pauldaddyadams]

Hi

I need help please with two formulas to help as I am building a staff planning spreadsheet.

Requirement 1
On the “Calls” tab it lists the times in which calls will be made. These are always an hour long.

I need on the “Snapshot” sheet to automatically pull this across and place an X in the cell if there is a call at a scheduled time.

Requirement 2
On the “Calls” sheet it has the employees start and end time and I need to show this on the sheet “snapshot”. Please note that a shift can be split up to three times.

Can anyone help please with any of these requirements?

I am not precious over the format so if it needs to be changed slightly then that’s not an issue.

I have uploaded an example to show what I need.

[MarvinP]

Hi Paul,

I'd be pivot tableing this data. See how I'd arrange it below your example then it could Pivot easier. I've given you a sample pivot but it can be expanded and displayed in different ways. See if this works for you and play/learn more about Pivots.
Staff Planner Pivot Table Call Stats.xlsx

[pauldaddyadams]

Hi Marvin

I did consider than but the problem I have is I need it to show blanks where people are working but have no calls scheduled. I also need it to show visually where the calls are back to back e.g. ideally an 8 hour shift they would be one after the after.

For context this is for carers where currently they travel to peoples homes and then care for them but currently the working hours of 8 hours a day are spread from 6am - 10pm which isnt efficient.

[pauldaddyadams]

Hi

Just a quick update I have managed to complete requirement two.

I only need requirement one completed where I think I need a sumproduct formula.


I have had to change it around a bit so now I need a formula which pulls Dates from the "Call Planner" and places an "X" in the "Weekly Schedule" where it matches:
Person name
Date
Start and End times

Can anyone help please?

[MarvinP]

Hi paul,

I still think you can get there with pivots. You need to drag different columns around and see what it gives you. What if you had the person as the filter and only do one at a time. What if you showed times if they had an call or not? Learn more about Pivots and see if no formulas or vba is needed.

[pauldaddyadams]

Hi

I have tried pivoting as suggested for a while and I just cant get it to display it how I need it to. I would have loved a pivot to be the answer as that would be easy.

My original ambition was to have on one page a view of the hours someone is due to work and then the calls scheduled throughout the day.

I have manged to show the hours worked on the “weekly schedule” but I thought I could use a formula which would place an “X” if a call was scheduled but I cant as those cells for the formatting are not blank.

Is there any way to achieve what I need?

If not, I can have a separate sheet to show the calls but I am stuck on the formula for this if anyone could offer any help?

The data is on the “call planner” tab and the “X” would need to be placed on the “Call Schedule” sheet.

I cant get it to work

[JeteMc]

Try pasting the following into cell C10 on the Call schedule sheet:

Please Login or Register  to view this content.

Let us know if you have any questions.

[pauldaddyadams]

Hi,

Thank you for taking a look at this!!

I have copied it into the attached but I have an error I cant resolve myself.

Person 1 works until 10am however on the summary sheet they are marked to work until 11am

Can you offer any help please?

[JeteMc]

I had thought the x's should show the times between the earliest start time and the latest end time.
To produce x's that correspond to start times only use:

Please Login or Register  to view this content.

Note that this is not an array entered formula, paste the formula into cell C10, press the Enter key and then copy over and down.
Let us know if you have any questions.

[pauldaddyadams]

This is great JeteMc - this is just what I need. A huge thank you

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.