CODEWITHSUNDEEP
excelexcel-formulaexcel-2007excel-2010
Punith Gubbi
Punith Gubbi

Reputation: 692

Alternate for Indirect used with Sumifs?

I am using the formula to ~300 cells which takes the address dynamically using INDIRECT function, but it is slowing down the calculation time.

please suggest me an alternative method to make the calculation faster.

I have done basics as it has macro with screen updating= false and calculation=xlmanual.

=SUMIFS(Sheet1!$L:$L,Sheet1!$I:$I,Bookings_QTD!$F51,Sheet1!$B:$B,Bookings_QTD!I$2,INDIRECT($I$8),$K$8,Sheet1!$C:$C,$M$8)/1000000

here cell $I$8 is dynamic where values will varies based eg:-Sheet5!$A:$A, Sheet5!$B:$B...

$I$8=Sheet5!$E:$E

I need to use it for many cells ~400+ with other criterias in future.

kindly suugest me better formula or method which will decrease the calculation time.
Thanks in advance

Upvotes: 0

Views: 801

Answers (1)

hnk
hnk

Reputation: 2214

INDIRECT() will be slow for many sheets as it is a Volatile function. i.e. every time there is a change in any cell in the workbook, it will get triggered.

If your cell values are relatively static. i.e. if you are indirectly referring to cell "B6" and you expect the content of B6 to remain the same and only expect the input of your function to change, say from "B6" to "Z8", you can use the following code:

Function MyIndirect(RangeStr as String) as Variant
    MyIndirect = ActiveSheet.Range("RangeStr").Value
End Function

This should work. And should you need to 'refresh' this value, simply run an Application.Calculation (i.e. press Ctrl-Alt-F9)

Upvotes: 1

Related Questions