Populate Calender

[windoz]

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

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.

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

 

[windoz]

Hi Fluff,

I've updated my account details, thank you.

I'm using Excel 2016 on a Windows Desktop/Laptop.

Thank you.

 

[Fluff]

Ok, thanks for that.
How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		09/01/2020	09/02/2020	09/03/2020	09/04/2020	09/05/2020	09/06/2020	09/07/2020	09/08/2020	09/09/2020	09/10/2020	09/11/2020	09/12/2020
2	Staff 1		S
3	Staff 2											S
4	Staff 3
5	Staff 4
6	Staff 5							S
7	Staff 6
8	Staff 7
9	Staff 8
10	Staff 9
11	Staff 10
12	Staff 11
13	Staff 12
Sheet1

Cell Formulas
Range	Formula
B2:M13	B2	=IFERROR(INDEX(Sheet2!$D$2:$D$100,MATCH($A2&"|"&B$1,Sheet2!$A$2:$A$100&"|"&Sheet2!$C$2:$C$100,0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[windoz]

Amazing!

This has saved me hours worth of work. I now need to look into array formulas!!

Thank you!!

 

[Fluff]

You're welcome & thanks for the feedback.