Hi,
I'm confused about how I should approach the following problem; (I did think of using index, but this messes up when I drag to replicate the formula)
I have a calendar this should list the absences which will be pulled from sheet 2, however the data will change on a daily basis, and some even have over 300 staff in the lists.
1. How can I populate the following layout which shows the absent code in the cell between staff and date ?
2. How can I automatically update any extra days which the calendar extends to ?
Sheet1
Sheet 2
Please if someone can advise or point me in the right direction I would appreciate this.
I'm confused about how I should approach the following problem; (I did think of using index, but this messes up when I drag to replicate the formula)
I have a calendar this should list the absences which will be pulled from sheet 2, however the data will change on a daily basis, and some even have over 300 staff in the lists.
1. How can I populate the following layout which shows the absent code in the cell between staff and date ?
2. How can I automatically update any extra days which the calendar extends to ?
Sheet1
01/09/2020 | 02/09/2020 | 03/09/2020 | 04/09/2020 | 05/09/2020 | 06/09/2020 | 07/09/2020 | 08/09/2020 | 09/09/2020 | 10/09/2020 | 11/09/2020 | 12/09/2020 | 13/09/2020 | |
Staff 1 | |||||||||||||
Staff 2 | |||||||||||||
Staff 3 | |||||||||||||
Staff 4 | |||||||||||||
Staff 5 | |||||||||||||
Staff 6 | |||||||||||||
Staff 7 | |||||||||||||
Staff 8 | |||||||||||||
Staff 9 | |||||||||||||
Staff 10 | |||||||||||||
Staff 11 | |||||||||||||
Staff 12 |
Sheet 2
Staff Name | Address | Date Absent | Absent Code | Site |
Staff 1 | 1 London | 02/09/2020 | S | A |
Staff 2 | 2 Manchester | 11/09/2020 | S | B |
Staff 3 | 3 Birmingham | 16/09/2020 | S | C |
Staff 4 | 4 Newcastle | 21/09/2020 | S | D |
Staff 5 | 5 North West | 07/09/2020 | S | E |
Staff 6 | 6 North East | 30/09/2020 | S | B |
Staff 7 | 7 South East | C | ||
Staff 8 | 8 South West | D | ||
Staff 9 | 9 England | E | ||
Staff 10 | 10 Scotland | B | ||
Staff 11 | 11 Wales | C | ||
Staff 12 | 12 Somewhere | D | ||
Staff 1 | 1 London | 25/09/2020 | S | A |
Staff 4 | 4 Newcastle | 01/10/2020 | S | D |
Please if someone can advise or point me in the right direction I would appreciate this.