Issue with table referencing for named ranges across multiple sheets

[JW2989]

Hi - I am trying to build a resource capacity tool but having problems with referencing and updating tables when new records are added.

1) Named Resources are added to the resource tab
2) Resources are loaded as a drop down in the Allocations tab, where they are matched with Initaitives, which includes their utilisation, Start & End dates

What I would like to show, is:

- list of all resources across a weekly timeline with how many days they have been allocated to that initiative

So far, I am trying to load all the resources into a daily timeline that plots out their allocation (IF (start and end date are between this week then give 1*utilisation). So if I am 50%, it would show 0.5 every day between start and end dates. I have then named each week as a named range (Week1, Week2) and then trying to look up SUM(FILTER(Week1, List of resources=name in this row).

My issue - when new names are added (or deleted) the referencing gets screwed up and all my named ranges change. I have tried a macro to clear the fields and reapply the formula but this does not seem to work.

How can I make the tables expand with the correct formulas - I have spent a week trying and cannot find any solution.

Any help would be greatly appreciated

Allocations:

ResourceManagement vXBKK.xlsx
	C	D	E	F	G	H	I	J
4	Full Name	Line Manager	Initiative	Domains	Utilisation	Start Date	End Date	Work Days
5	Jack Watson	Christine Agart	Wireless Survey	Infrastructure	50%	01/01/2022	10/03/2022	49
6	John Jones	Christine Agart	Wireless Survey	Infrastructure	100%	01/02/2022	30/04/2022	64
7	Katie Marsh	Christine Agart	EBS Workday	Procurement	50%	01/03/2022	01/04/2022	24
8	Katie Marsh	Christine Agart	New Salesforce App	Sales	100%	15/01/2022	15/02/2022	22
9	Sarah Beecher	John Davies	EBS Workday	Procurement	100%	01/01/2022	31/12/2022	260
10	Jack Watson	Christine Agart	EBS Workday	Procurement	100%	01/02/2022	01/04/2022	44
11	Bill Jean	Ash Barrett	ISIM 2.3	Procurement	100%	01/04/2022	01/06/2022	44
12	John Jones	Christine Agart	Digital trends	Digital	100%	01/01/2022	01/06/2022	108
13	Florence HB	Jack Watson	LAN Modernisation	Infrastructure	100%	01/01/2022	22/04/2022	80
14	Florence HB	Jack Watson	LAN Modernisation	Infrastructure	100%	10/01/2022	10/02/2022	24
Allocations

Cell Formulas
Range	Formula
D5:D14	D5	=VLOOKUP([@[Full Name]],Table9,2,FALSE)
F5:F14	F5	=VLOOKUP([@Initiative],Initiatives,2,FALSE)
J5:J14	J5	=NETWORKDAYS(Allocations!$H5,Allocations!$I5)

Named Ranges
Name	Refers To	Cells
Initiatives	=Initiatives!$D$4:$E$12	F5:F14
List000	=Table9[Full Name]	D5:D14
ListInitiatives	=Tbl_Initiaitives[Initiative]	F5:F14

Cells with Data Validation
Cell	Allow	Criteria
E4:E14	List	=ListInitiatives
C4:C14	List	=List000

Daily View:

ResourceManagement vXBKK.xlsx
	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
2								Mon	Tue	Wed	Thu	Fri	Mon	Tue	Wed	Thu	Fri	Mon	Tue	Wed	Thu	Fri	Mon	Tue	Wed	Thu	Fri	Mon
3								03/01/2022	04/01/2022	05/01/2022	06/01/2022	07/01/2022	10/01/2022	11/01/2022	12/01/2022	13/01/2022	14/01/2022	17/01/2022	18/01/2022	19/01/2022	20/01/2022	21/01/2022	24/01/2022	25/01/2022	26/01/2022	27/01/2022	28/01/2022	31/01/2022
4	Full Name	Line Manager	Initiative	Domains	Utilisation	Start Date	End Date	03/01/2022	04/01/2022	05/01/2022	06/01/2022	07/01/2022	10/01/2022	11/01/2022	12/01/2022	13/01/2022	14/01/2022	17/01/2022	18/01/2022	19/01/2022	20/01/2022	21/01/2022	24/01/2022	25/01/2022	26/01/2022	27/01/2022	28/01/2022	31/01/2022
5	Bill Jean	Ash Barrett	ISIM 2.3	Procurement	100%	01/04/2022	01/06/2022	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	Florence HB	Jack Watson	LAN Modernisation	Infrastructure	100%	01/01/2022	22/04/2022	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
7	Florence HB	Jack Watson	LAN Modernisation	Infrastructure	100%	10/01/2022	10/02/2022	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
8	Jack Watson	Christine Agart	Wireless Survey	Infrastructure	50%	01/01/2022	10/03/2022	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5	0.5
9	Jack Watson	Christine Agart	EBS Workday	Procurement	100%	01/02/2022	01/04/2022	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	John Jones	Christine Agart	Wireless Survey	Infrastructure	100%	01/02/2022	30/04/2022	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11	John Jones	Christine Agart	Digital trends	Digital	100%	01/01/2022	01/06/2022	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
12	Katie Marsh	Christine Agart	EBS Workday	Procurement	50%	01/03/2022	01/04/2022	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	Katie Marsh	Christine Agart	New Salesforce App	Sales	100%	15/01/2022	15/02/2022	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1
14	Sarah Beecher	John Davies	EBS Workday	Procurement	100%	01/01/2022	31/12/2022	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
ResourcebyDay

Cell Formulas
Range	Formula
J2:AD2	J2	=TEXT(J4,"ddd")
J5:AD14	J5	=$G5*IF(AND($H5<=J$3,$I5>=J$3),1,0)

Weekly View:

ResourceManagement vXBKK.xlsx
	C	D	E	F	G	H	I
4			January
5	Full Name	Line Manager	03/01/2022	10/01/2022	17/01/2022	24/01/2022	31/01/2022
6	Jack Watson	Christine Agart	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
7	John Jones	Christine Agart	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
8	Katie Marsh	Christine Agart	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
9	Katie Marsh	Christine Agart	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
10	Sarah Beecher	John Davies	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
11	Jack Watson	Christine Agart	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
12	Bill Jean	Ash Barrett	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
13	John Jones	Christine Agart	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
14	Florence HB	Jack Watson	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
15	Florence HB	Jack Watson	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
ResourcebyWeek

Cell Formulas
Range	Formula
E6:I15	E6	=SUM(FILTER(Week1,ResourceNameDay=$C6))

Named Ranges
Name	Refers To	Cells
ResourceNameDay	=Table_Query_from_Excel_Files_12[Full Name]	E6:I15
Week1	=ResourcebyDay!$J$5:$N$11	E6:I15

 

[JW2989]

SOLVED- I had changed the structure of the original query so it was not refreshing!