Alternate for Indirect and Sumproduct formula

R

Rahul Malhotra

New member
Joined
Apr 18, 2018
Messages
43
Reaction score
0
Points
0
Excel Version(s)
2013, 2016
Hi, i have applied Indirect cum Sumproduct formula to my 6 worksheets in a workbook but after applying the same its become damn slow then i researched at google and found that Indirect formula makes your file slow to process.

So if you can please do provide any other alternate for Indirect cum Sumproduct formula which i am currently using as below:

Code: 
=SUMPRODUCT(
(INDIRECT("'"&'Report'!$AM$1&"'!G$8:R$8")=H$6)*
(INDIRECT("'"&'Report'!$AM$1&"'!$C$10:$C$59")=$B8)*
(INDIRECT("'"&'Report'!$AM$1&"'!$D$10:$D$59")=$D8)*
(INDIRECT("'"&'Report'!$AM$1&"'!$G$10:$R$59")))

Any help would be appreciated. Thank you in advance.
 
Last edited:
That formula doesn't use large ranges and shouldn't cause sheet to be slow unless you have that formula in many cells. Unfortunately if you are looking to a specific cell for the name of another sheet to get your results, there aren't really any alternatives to INDIRECT().
 
Is this formula in only one cell on each of the 6 sheets? If so, the formula shouldn't noticeably slow the worksheet down (I tried it here). Is the formula in your sheets looking at that 50*12 range, or a much bigger range? Perhaps the slowdown is because the workbook is frequently recalculating all formulae. You can test thsi by setting your Calculation options to Manual and then using the Calculate now button (or F9 on the keyboard) and see how quickly it calculates once. At the moment I can't see a way of allowing you to change AM1 on the Report sheet to get the different results on the 6 sheets. Without recourse to vba that is.

edit post posting: NBVC beat me to it.
 
In a worksheet the formula have been used around more than 100 times so with the 6 worksheets around 600 formula. When i am trying to pull the data in Report tab from all those 6 tabs by using the above formula the calculation process taking a hell of time to throw the output.

I can not switch to manual process for calculation and The reason is if there any change in the dropdown selection. The output does not change and it changes only when the calculation process is automatic. By changing the dropdown selection takes more than a minute to throw an output.

Is there any way where i can speedup the calculation process when there is any change in the dropdown selection without changing the calculation process to manual.
 
Last edited:
Rahul Malhotra said:
I can not switch to manual process for calculation and The reason is if there any change in the dropdown selection. The output does not change and it changes only when the calculation process is automatic.
Click to expand...
I'm only asking you to do that temporarily to see how long it takes for a single whole-workbook recalculation. If that by itself takes a long time then yes, looking for a better formula is the way to go (I can't think of one just now except for vba to update the formulae without INDIRECT each time Report!AM1 changes), but if the sheet/workbook is frequently recalculating due to volatile functions (not necessarily just in the formula you've cited), such as today(), now(), offset, then perhaps we neeed to look elsewhere on the sheets too.
 
Its taking around one and half minute to throw the expected results by changing the dropdown selection. There is no today(), and now() have been used in the workbook but yes offset have been used.
 
I have major used two formula for offset in my workbook for eg. =SUM(OFFSET(G9,0,0,1,Y$7)) and =SUM(OFFSET(H8,0,0,1,VLOOKUP(AH$4,AK$4:AL$18,2,FALSE)))-IF(AH$3>3,K8,0)-IF(AH$3>6,O8,0)-IF(AH$3>9,S8,0).

So if you have any other alternate for above two then please help.
 
Rahul Malhotra said:
=SUM(OFFSET(G9,0,0,1,Y$7))
Click to expand...
=SUM(G9:INDEX(G9:ZZ9,0,Y$7))
or
=SUM(INDEX(9:9,7):INDEX(9:9,Y$7+6))

Rahul Malhotra said:
=SUM(OFFSET(H8,0,0,1,VLOOKUP(AH$4,AK$4:AL$18,2,FALSE)))-IF(AH$3>3,K8,0)-IF(AH$3>6,O8,0)-IF(AH$3>9,S8,0)
Click to expand...
=SUM(H8:INDEX(H8:ZZ8,VLOOKUP(AH$4,AK$4:AL$18,2,FALSE)))-IF(AH$3>3,K8,0)-IF(AH$3>6,O8,0)-IF(AH$3>9,S8,0)

in both cases the reference to the column ZZ should be adjusted so that it will always include the data, but I suspect it's best to avoid XFD.
 
Last edited:
Can you please also provide the alternate solution for the second offset formula =SUM(OFFSET(H8,0,0,1,VLOOKUP(AH$4,AK$4:AL$18,2,FALSE)))-IF(AH$3>3,K8,0)-IF(AH$3>6,O8,0)-IF(AH$3>9,S8,0)
 
Rahul Malhotra said:
Can you please also provide the alternate solution for the second offset formula =SUM(OFFSET(H8,0,0,1,VLOOKUP(AH$4,AK$4:AL$18,2,FALSE)))-IF(AH$3>3,K8,0)-IF(AH$3>6,O8,0)-IF(AH$3>9,S8,0)
Click to expand...

See msg#9
 
Thank you so much p45callet me try by changing the offset formula in all the 6 worksheets. I believe after applying the INDEX formula the report tab should provide the output faster then the present.

Is it required to change in the report tab formula as well which i have provided in very first (Indirect Cum Sumproduct) in msg#1 or that is not the bad boy to making slow process.
 
because the msg#1 formula i have used for around 10 worksheets and in each worksheet around more than 80 or 90 times.
 
See how it goes. Change them if it's still too slow.
 
Hi p45cal, yes it was too slow and that was only reason because of Indirect formula. I have changed from Indirect to Index for all the 15 worksheets and now the output process speed become quite fast while changing the selection from the drop downs. Thank you so much for your support.
 
You must log in or register to reply here.
Back
Top