Hey Everyone,
I have a rather large and probably complex sheet I am working on. It is(or will be) comprised of weekly schedule with a row for each hour of the day and column for each day. This will be a 24/7 schedule. The plan is for the schedule to automatically populate the names of by employee who into every cell(hour) that they are working throughout the week. To the side of the schedule is a list of the employees names, their start and end times and the days that they work. The idea behind this is that I can select the start and end times for each employee as well as the days of the week they work. Based on that information, it will populate the week and hours within each day with the employees names, separated by a "/". I know this may be hard to explain so I will attach the spreadsheet so you can get a full view of how everything is put together.
Link to spreadsheet: https://1drv.ms/x/s!At5thfaIZBTcgctfHJgvPUwuYZ4f5g
Please open in Excel, not Excel online. Otherwise, ActiveX controls wont work .
Example:
<tbody>
</tbody>
I have the list of names and times completed. The thing that is tripping me up is the formula or array that I would put into the weekly schedule that would pull the names. At one point I though I had a formula that would work but it was about 12 IF nested statements with AND statements gallor, which only gave me about half of the day's worth of names. I have left a number of the cells with the original formula I created so you can see what I tried doing(its embarrassing). Since the department I am creating this for is a 24/7 department, the biggest stumbling block I had was getting the early hours of the day to reflect schedules that would roll over into the next day.
Any help on how to approach a formula or array that would populate the schedule would be super helpful.
I have a rather large and probably complex sheet I am working on. It is(or will be) comprised of weekly schedule with a row for each hour of the day and column for each day. This will be a 24/7 schedule. The plan is for the schedule to automatically populate the names of by employee who into every cell(hour) that they are working throughout the week. To the side of the schedule is a list of the employees names, their start and end times and the days that they work. The idea behind this is that I can select the start and end times for each employee as well as the days of the week they work. Based on that information, it will populate the week and hours within each day with the employees names, separated by a "/". I know this may be hard to explain so I will attach the spreadsheet so you can get a full view of how everything is put together.
Link to spreadsheet: https://1drv.ms/x/s!At5thfaIZBTcgctfHJgvPUwuYZ4f5g
Please open in Excel, not Excel online. Otherwise, ActiveX controls wont work .
Example:
| A | B | C | D | |
| 1 | Time | Sunday | Monday | Tuesday |
| 2 | 7:00 AM | Emp 1 | Emp 1 | Emp 1 |
| 3 | 8:00 AM | Emp 1/Emp 2 | Emp 1/Emp 2/Emp 3 | Emp 1/Emp3 |
| 4 | 9:10 AM | Emp 2 | Emp 2/Emp 3 | Emp 3 |
| 5 | 10:00 AM | Emp 2 | Emp 2/Emp 3 | Emp3 |
<tbody>
</tbody>
I have the list of names and times completed. The thing that is tripping me up is the formula or array that I would put into the weekly schedule that would pull the names. At one point I though I had a formula that would work but it was about 12 IF nested statements with AND statements gallor, which only gave me about half of the day's worth of names. I have left a number of the cells with the original formula I created so you can see what I tried doing(its embarrassing). Since the department I am creating this for is a 24/7 department, the biggest stumbling block I had was getting the early hours of the day to reflect schedules that would roll over into the next day.
Any help on how to approach a formula or array that would populate the schedule would be super helpful.
