Replace INDIRECT(dynamic range) with non-volatile function

[AntonyC760]

Hello experts,

I have a spreadsheet calculating weekly data based on different criteria. There are 52 rows of this formula and multiple columns for different criteria

The following formula counts # of Tickets Open from “Project1!E2:E79” that fall within the 7 days range
=IF($E$1<$A8,0, COUNTIFS(INDIRECT($B$3),"<"&($A8+1),INDIRECT($B$3),">"&($A8-7)))

A8 is date // eg: 1/1/2022
$B$1 = Project1 //This is the cell user can select project name which corresponds to source data worksheet. eg: Project1, Project2, Project3…etc worksheets in the workbook
$B$2 = COUNTA(INDIRECT($B$1&"!$A:$A")) //count # of rows in column D in Project1 worksheet)
$B$3 = $B$1&"!E2:E"&$B$2 //This creates the range for countifs condition. Eg: Project1!E2:E79 where Column E is Date

$B$3 is a dynamic range depending on the selected project and also source data is updated weekly.
Is there a way to replace INDIRECT($B$3) with a non-volatile function?
The workbook has hundreds of cell which contains similar formula with different matching criteria. And the source data for each project can grow as large as 80,000 rows as the time goes
I am trying to see if there is a way to reduce the calculation time.

The following is an example of the formula I am using

Example.xlsx
	A	B	C	D	E
1	Project	Project1		Today	4/8/22
2	Rows	79
3	Data Range	Project1!E2:E79
4
5
6
7	Date	Tickets Open
8	1/1/22	5
9	1/8/22	12
10	1/15/22	7
11	1/22/22	8
12	1/29/22	1
13	2/5/22	7
14	2/12/22	8
15	2/19/22	2
16	2/26/22	5
17	3/5/22	12
18	3/12/22	9
19	3/19/22	2
20	3/26/22	0
21	4/2/22	0
22	4/9/22	0
23	4/16/22	0
24	4/23/22	0
25	4/30/22	0
26	5/7/22	0
27	5/14/22	0
28	5/21/22	0
29	5/28/22	0
30	6/4/22	0
31	6/11/22	0
32	6/18/22	0
33	6/25/22	0
34	7/2/22	0
35	7/9/22	0
36	7/16/22	0
37	7/23/22	0
38	7/30/22	0
39	8/6/22	0
40	8/13/22	0
41	8/20/22	0
42	8/27/22	0
43	9/3/22	0
44	9/10/22	0
45	9/17/22	0
46	9/24/22	0
47	10/1/22	0
48	10/8/22	0
49	10/15/22	0
50	10/22/22	0
51	10/29/22	0
52	11/5/22	0
53	11/12/22	0
54	11/19/22	0
55	11/26/22	0
56	12/3/22	0
57	12/10/22	0
58	12/17/22	0
59	12/24/22	0
Summary

Cell Formulas
Range	Formula
E1	E1	=TODAY()
B2	B2	=COUNTA(INDIRECT($B$1&"!$A:$A"))
B3	B3	=$B$1&"!E2:E"&$B$2
B8:B59	B8	=IF($E$1<$A8,0, COUNTIFS(INDIRECT($B$3),"<"&($A8+1),INDIRECT($B$3),">"&($A8-7)))
A9:A59	A9	=A8+7

Cells with Data Validation
Cell	Allow	Criteria
B1	List	Project1, Project2, Project3

Project1 worksheet

Ticket #	Type1	Type2	Type3	Date
1	A	AA	AAA	1/1/22
2	A	AA	AAA	1/1/22
3	D	DD	DDD	1/1/22
4	F	FF	FFF	1/1/22
5	R	RR	RRR	1/1/22
6	H	HH	HHH	1/6/22
7	A	AA	AAA	1/7/22
25	A	AA	AAA	1/7/22
26	D	DD	DDD	1/7/22
27	F	FF	FFF	1/7/22
28	R	RR	RRR	1/7/22
29	H	HH	HHH	1/7/22
8	A	AA	AAA	1/8/22
11	A	AA	AAA	1/8/22
12	D	DD	DDD	1/8/22
13	F	FF	FFF	1/8/22
14	R	RR	RRR	1/8/22
9	H	HH	HHH	1/9/22
38	A	AA	AAA	1/9/22
39	A	AA	AAA	1/9/22
40	D	DD	DDD	1/9/22
41	F	FF	FFF	1/9/22
10	R	RR	RRR	1/10/22
15	H	HH	HHH	1/15/22
16	A	AA	AAA	1/16/22
17	A	AA	AAA	1/17/22
18	D	DD	DDD	1/18/22
20	F	FF	FFF	1/18/22
21	R	RR	RRR	1/18/22
22	H	HH	HHH	1/18/22
23	A	AA	AAA	1/18/22
19	A	AA	AAA	1/19/22
24	D	DD	DDD	1/24/22
30	F	FF	FFF	1/30/22
31	R	RR	RRR	1/31/22
32	H	HH	HHH	2/1/22
33	A	AA	AAA	2/2/22
34	A	AA	AAA	2/3/22
35	D	DD	DDD	2/4/22
36	F	FF	FFF	2/5/22
37	R	RR	RRR	2/6/22
42	H	HH	HHH	2/11/22
43	A	AA	AAA	2/12/22
45	A	AA	AAA	2/12/22
46	D	DD	DDD	2/12/22
47	F	FF	FFF	2/12/22
48	R	RR	RRR	2/12/22
49	H	HH	HHH	2/12/22
44	A	AA	AAA	2/13/22
50	A	AA	AAA	2/19/22
51	D	DD	DDD	2/20/22
52	F	FF	FFF	2/21/22
53	R	RR	RRR	2/22/22
54	H	HH	HHH	2/23/22
57	A	AA	AAA	2/26/22
58	A	AA	AAA	2/27/22
59	D	DD	DDD	2/28/22
55	F	FF	FFF	3/1/22
56	R	RR	RRR	3/1/22
60	H	HH	HHH	3/1/22
61	A	AA	AAA	3/2/22
62	A	AA	AAA	3/3/22
64	D	DD	DDD	3/3/22
65	F	FF	FFF	3/3/22
66	R	RR	RRR	3/3/22
67	H	HH	HHH	3/3/22
63	A	AA	AAA	3/4/22
70	A	AA	AAA	3/11/22
68	D	DD	DDD	3/12/22
69	F	FF	FFF	3/12/22
71	R	RR	RRR	3/12/22
74	H	HH	HHH	3/12/22
75	A	AA	AAA	3/12/22
76	A	AA	AAA	3/12/22
77	D	DD	DDD	3/12/22
78	F	FF	FFF	3/12/22
72	R	RR	RRR	3/13/22
73	H	HH	HHH	3/14/22

 

[Fluff]

Hi & welcome to MrExcel.
How many projects are you likely to have?

 

[AntonyC760]

Hi & welcome to MrExcel.
How many projects are you likely to have?

Hello Fluff,

I will have 3 projects from 3 different years.
Eg:
Project1 (from 2022)
Project2 (from 2021)
Project3 (from 2020)

Thank you!

 

[Fluff]

In that case you can use

=LET(Rng,SWITCH($B$1,"project1",Project1!E:E,"Project2",Project2!E:E,"Project3",Project3!E:E),IF($E$1<$A8,0, COUNTIFS(Rng,"<"&($A8+1),Rng,">"&($A8-7))))

 

[AntonyC760]

Thank you Fluff for the feedback! The formula works.
I will create 3 separate Summary worksheet to calculate data for each of the project. This way I can make a comparison chart on these projects
eg: Summary1 (calculate Project1 data), Summary2 (calculate Project2 data), Summary3 (calculate Project3 data).

I will also allow user to change the Project name as needed. (Project1, Project2, Project3 worksheet names will also be changed accordingly)

Is there a way to make the Project1!E:E in the formula below a dynamic variable?
=LET(Rng,Project1!E:E,IF($E$1<$A8,0, COUNTIFS(Rng,"<"&($A8+1),Rng,">"&($A8-7))))

I tried the following, excel returns #VALUE!
=LET(Rng,$B$1&"!E:E",IF($E$1<$A8,0, COUNTIFS(Rng,"<"&($A8+1),Rng,">"&($A8-7))))

The following formula returns 0 on all calculation
=LET(Rng,$B$3,IF($E$1<$A8,0, COUNTIFS(Rng,"<"&($A8+1),Rng,">"&($A8-7))))

Range	Formula
Cell Formulas
E1	E1	=TODAY()
B2	B2	=COUNTA(INDIRECT($B$1&"!$A:$A"))
B3	B3	=$B$1&"!E2:E"&$B$2

Thank you!

 

[Fluff]

You can either do it the way I showed, or by using indirect.

 

[AntonyC760]

Thank you for the quick response!

 

[Fluff]

You're welcome & thanks for the feedback.