Forum Discussion
Automatic schedule
Hi. I am working on a to be able to staff our ice rink for the coming season. I have taken over an old schedule. But I see that there are a lot of things that could perhaps be fixed automatically. I send out an Excel sheet to a number of people who can fill in the dates they can help. Then I have to sit and bring in the different people on different dates. Different people know different jobs. In the picture you can see how it looks today. I would like to send out a Google spreadsheet with dates and names then they can fill in the dates they can work. Then I would like to connect Excel to this spreadsheet. Download all data to Excel. Then I want Excel to place the different people in the schedule. Diffrent people can du diffrent work. To the right you see name with numbers like "patrick 2378" tells that Patrick can do work 2378 and that is column I,J,P and Q. I hope you understand!
Here's a solution using FILTER.
For the scheduling availability, I simplified and removed the names from cells. Instead of "Patrick 2378", it's simply 2378. Additionally, there's a matter of what to do if 3 people are available for 8 and there's only 2 slots. For this demo, I included a 3rd slot for 8.
This formula is applied the first time a given slot appears. I've highlighted the columns in the workbook.
Base formula:
=IFERROR(FILTER($O$1:$S$1,ISNUMBER(SEARCH(B$1,$O2:$S2))),"")
