Speed up INDIRECT formula (or find alternative)

[easteregg]

Hey folks,
trying to build a sales report in Excel, which sums up all the sales on a product level.
For each product I have multiple sales people, each of which has his own sheet.

Lets say this is the structure:

Formula-Sheet

Report-Apples

Apples-John

Apples-Mary

Apples-Donald

<tbody>
</tbody>

Formula-Sheet

	A
1	Apples-John
2	Apples-Mary
3	Apples-Donald

<tbody>
</tbody>

A1:A3 = Named Range "AppleSheets"

Report-Apples:

	A	B	C
1	Date	Apples Sold (Total)	Revenue (Total)
2	01.01.2018	6	12
3	02.01.2018	5	10
	...	...	...

<tbody>
</tbody>

B2=SUMPRODUCT(SUMIF(INDIRECT("'"&AppleSheets&"'"&"!A"&ROW()),A2,INDIRECT("'"&AppleSheets&"'"&"!B"&ROW())))
C2=SUMPRODUCT(SUMIF(INDIRECT("'"&AppleSheets&"'"&"!A"&ROW()),A2,INDIRECT("'"&AppleSheets&"'"&"!C"&ROW())))

Apples-John:

	A	B	C
1	Date	Apples Sold	Revenue
2	01.01.2018	5	10
3	02.01.2018	0	0
	...	...	...

<tbody>
</tbody>

Apples-Mary:

	A	B	C
1	Date	Apples Sold	Revenue
2	01.01.2018	1	2
3	02.01.2018	4	8
	...	...	...

<tbody>
</tbody>

Apples-Donald:

	A	B	C
1	Date	Apples Sold	Revenue
2	01.01.2018	0	0
3	02.01.2018	1	2
	...	...	...

<tbody>
</tbody>

Since I have ~20 Sheets like Report-Apples, and do it for the whole year, those INDIRECT formulas seem to slow down the calculations enormously.

Is there another way to solve this, which is not including INDIRECT?
Or is there a way to speed up INDIRECT?

Thanks in advance,
Jan

 

[arthurbr]

If all sheets have the same structure ( which seems to be the case) why not use a 3D reference sum

If for any reason sheets will be added or deleted create an empty sheet called "start" ( or whatever) before the 1st sheet and another one at the end called "End" (or whatever) and use those two sheets as first and last for your 3-D ref.
When you add sheet(s) just add them between Start end End