Resource planner with adaptive cells

[W1nu]

Hi Guys,

I have recieved some task as an intern this summer, and one of them is to create a resource planner for the company I am working at. Each of their projecct have 4 distinct phases, and they want a stacked gant-chart with a resource table at the bottom, so they can plan their projects, have an overlook over the number of people they need each month/year, and make some graphics out of it. I created the gant chart with some conditional formatting and a scroller, and copied the dates down to the table so they match. Now, when i scroll, the timetstamps will uptdate simoultanously, but I wish the cells in the table to do the same. I.E, If I scroll from 2020 to 2021, I want the table to shift from the filled columns to empty, fillable columns. The previous data in the cells also needs to be stored somehow.

I am fairly new to excel, so I was wondering if something like this is possible? And if so do you have any tips to how I can ccomplish this?

Resource planner.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX
1
2																												0
3
4
5															2020												2021												2022
6		Phase1			Phase 2			Phase 3			Phase 4				jan	feb	mar	apr	mai	jun	jul	aug	sep	okt	nov	des	jan	feb	mar	apr	mai	jun	jul	aug	sep	okt	nov	des	jan	feb	mar	apr	mai	jun	jul	aug	sep	okt	nov	des
7		Start	End		Start	End		Start	End		Start	End
8	Project 1	01.01.2020	01.02.2020		01.04.2020	05.05.2020		05.05.2020	09.07.2020		12.08.2020	13.12.2020
9
10	Project 2	02.03.2020	01.04.2020		01.05.2020	01.06.2020		01.07.2020	01.07.2021		07.08.2021	01.10.2022
11
12		02.10.2020	03.10.2023
13
14
15
16
17
18
19
20
21															2020												2021												2022
22													Team	Resources	jan	feb	mar	apr	mai	jun	jul	aug	sep	okt	nov	des	jan	feb	mar	apr	mai	jun	jul	aug	sep	okt	nov	des	jan	feb	mar	apr	mai	jun	jul	aug	sep	okt	nov	des
23													Eng	Drilling engineer	1	1	1	1	1	1	1	1	1	1	1	1
24													Eng	Completion engineer	3	3	3	3	3	3	3	3	3	3	3	3
25													Eng	Lead Drilling eng	5	5	5	5	5	5	5	5	5	5	5	5
26													Eng	Lead completion eng	76	76	76	76	76	76	76	76	76	76	76	76
27													Eng	Testing eng	8	8	8	8	8	8	8	8	8	8	8	8
28													Eng	Subsea compl eng	5	5	5	5	5	5	5	5	5	5	5	5
29													Eng	Subsea wo eng	6	6	6	6	6	6	6	6	6	6	6	6
30													Eng	LWI/ Intervention eng
31													Eng	fefwef	2	2	2	2	2	2	2	2	2	2	2	2
49													Admin	Tech ***	9	9	9	9	9	9	9	9	9	9	9	9
50														Total headcount	115	115	115	115	115	115	115	115	115	115	115	115	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
51
52
Sheet1

Cell Formulas
Range	Formula
O5,AA5,AM5,O21,AA21,AM21	O5	=YEAR(O6)
O6,O22	O6	=DATE(2020+$AB$2,1,1)
P6:AX6,P22:AX22	P6	=EDATE(O6,1)
O50:AX50	O50	=IF(O$22="","",SUBTOTAL(9,O23:O49))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O13:X13,O8:AX12,O14:AX17,Z13:AJ13,AL13:AV13,AX13	Expression	=AND(O$6 >=$K8; O$6 <= $L8)	text	NO
O13:X13,O8:AX12,O14:AX17,Z13:AJ13,AL13:AV13,AX13	Expression	=AND(O$6 >=$H8; O$6 <= $I8)	text	NO
O13:X13,O8:AX12,O14:AX17,Z13:AJ13,AL13:AV13,AX13	Expression	=AND(O$6 >=$E8; O$6 <= $F8)	text	NO
O13:X13,O8:AX12,O14:AX17,Z13:AJ13,AL13:AV13,AX13	Expression	=AND(O$6 >=$B8; O$6 <= $C8)	text	NO
AB2	Expression	=AND(Y$6 >=$K13; Y$6 <= $L13)	text	NO
AB2	Expression	=AND(Y$6 >=$H13; Y$6 <= $I13)	text	NO
AB2	Expression	=AND(Y$6 >=$E13; Y$6 <= $F13)	text	NO
AB2	Expression	=AND(Y$6 >=$B13; Y$6 <= $C13)	text	NO