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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Formula-Sheet[/TD]
[TD]Report-Apples[/TD]
[TD]Apples-John[/TD]
[TD]Apples-Mary[/TD]
[TD]Apples-Donald[/TD]
[/TR]
</tbody>[/TABLE]
Formula-Sheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apples-John[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apples-Mary[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Apples-Donald[/TD]
[/TR]
</tbody>[/TABLE]
A1:A3 = Named Range "AppleSheets"
Report-Apples:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Apples Sold (Total)[/TD]
[TD]Revenue (Total)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01.01.2018[/TD]
[TD]6[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02.01.2018[/TD]
[TD]5[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]
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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Apples Sold[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01.01.2018[/TD]
[TD]5[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02.01.2018[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]
Apples-Mary:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Apples Sold[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01.01.2018[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02.01.2018[/TD]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]
Apples-Donald:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Apples Sold[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01.01.2018[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02.01.2018[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]
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
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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Formula-Sheet[/TD]
[TD]Report-Apples[/TD]
[TD]Apples-John[/TD]
[TD]Apples-Mary[/TD]
[TD]Apples-Donald[/TD]
[/TR]
</tbody>[/TABLE]
Formula-Sheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apples-John[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apples-Mary[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Apples-Donald[/TD]
[/TR]
</tbody>[/TABLE]
A1:A3 = Named Range "AppleSheets"
Report-Apples:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Apples Sold (Total)[/TD]
[TD]Revenue (Total)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01.01.2018[/TD]
[TD]6[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02.01.2018[/TD]
[TD]5[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]
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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Apples Sold[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01.01.2018[/TD]
[TD]5[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02.01.2018[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]
Apples-Mary:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Apples Sold[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01.01.2018[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02.01.2018[/TD]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]
Apples-Donald:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Apples Sold[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01.01.2018[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02.01.2018[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]
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