Dynamic cell reference in indirect function

[okvals48]

This formula is pretty know when you want to sum cells across multiple sheets +SUMPRODUCT(SUMIFS(INDIRECT("'"&B19:B21&"'!d3:d14"),INDIRECT("'"&B19:B21&"'!B3:B14"),$B3))
I am interested is it even possible to make this part dynamic, when i pull the formula it automatically change references, >d3:d14<, i reaserched the internet but it seems always this part is static.
I can make relative cell referenc for first elemet d3, but for other one it does not allow it.

Basically i am trying to find the best solution for summing across multiple sheets, that is flexible, i account that i will have to add rows and columns, in C3 i was testing somthing

Book1.xlsx
	B	C	D	E	F	G	H
2		1	2	3	4	5	6
3	A		60
4	B		63
5	C		66
6	D		69
7	E		72
8	F		75
9	G		78
10	H		81
11	I		84
12	J		87
13	K		0
14	L		0
15
16
17
18
19	Sheet2
20	Sheet3
21	Sheet4
Sheet1

Cell Formulas
Range	Formula
D3:D14	D3	=+SUMPRODUCT(SUMIFS(INDIRECT("'"&$B$19:$B$21&"'!d3:d14"),INDIRECT("'"&$B$19:$B$21&"'!B3:B14"),$B3))

Data range, just coppy it on several sheets

Book1.xlsx
	B	C	D	E	F	G	H
2		1	2	3	4	5	6
3	A	10	20	30	40	50	60
4	B	11	21	31	41	51	61
5	C	12	22	32	42	52	62
6	D	13	23	33	43	53	63
7	E	14	24	34	44	54	64
8	F	15	25	35	45	55	65
9	G	16	26	36	46	56	66
10	H	17	27	37	47	57	67
11	I	18	28	38	48	58	68
12	J	19	29	39	49	59	69
Sheet2

 

[Saba Sabaratnam]

You could enter the following cell references in cells and refer them in INDIRECT formula .


$B$19:$B$21 = A1 (or any cell)
d3:d14 = A2 (or any cell)


=SUMPRODUCT(SUMIFS(INDIRECT("'"&A1&"'!"&A2),INDIRECT("'"&$B$19:$B$21&"'!B3:B14"),$B3))

Kind regards

Saba

 

[Tetra201]

Is this what you need?

=SUMPRODUCT(SUMIFS(INDIRECT(ADDRESS(3,COLUMN(D:D),,,$B$19:$B$21)&":"&ADDRESS(14,COLUMN(D:D),,)),INDIRECT("'"&$B$19:$B$21&"'!B3:B14"),$B3))