Changing formulae has dramatically slowed down my VBA code, yet the two do not seem to be connected

[ReproRetro]

I’m using Excel 2010 under Windows 7, but the workbook is saved in 97-2003 format.
I have two sheets, one is a “dashboard”, which has all the formulae and the second (called “HPOV_SQL”) holds the data. The dashboard uses a lot of similar formulae. And I have some VBA code that displays a table of information on the dashboard too.

A typical formula is:
=SUMPRODUCT((INT(HPOV_SQL!E1:E17000)<B$61)*((INT(HPOV_SQL!G1:G17000>=B$61))+(HPOV_SQL!G1:G17000=""))*(HPOV_SQL!A1:A17000=A6))

All was working fine until I changed to formula to use “Indirect”.
SUMPRODUCT compares columns of equal length. If there is no upper limit on the column, the formula is slow. So I made the limit 17000 rows. The current actual number of rows is about 14000, but it grows daily and one day the 17000 will be exceeded.
So I decided to store the number of rows in a cell on the dashboard sheet and use INDIRECT to replace the 17000’s in the formulae.

So now a typical formula is:
=SUMPRODUCT((INT(HPOV_SQL!E1:INDIRECT("HPOV_SQL!E"&D60))<B$61)*((INT(HPOV_SQL!G1:INDIRECT("HPOV_SQL!G"&D60)>=B$61))+(HPOV_SQL!G1:INDIRECT("HPOV_SQL!G"&D60)=""))*(HPOV_SQL!A1:INDIRECT("HPOV_SQL!a"&D60)=A6))

Were D60 on the dashboard holds the actual number of cells.

All the formulae work, but the table displayed on the dashboard by VBA code has almost ground to a halt and I can’t see why. I don’t do anything in code when cells change in the dashboard and I don’t access the formulae in code.
Has anyone any ideas?

 

[RoryA]

INDIRECT is a volatile formula which means it has to be recalculated whenever the workbook recalculates, even if none of its inputs have actually changed. Try using INDEX instead:
=SUMPRODUCT((INT(HPOV_SQL!E1:INDEX(HPOV_SQL!E:E,D60))=B$61))+(HPOV_SQL!G1:INDEX(HPOV_SQL!G:G,D60)=""))*(HPOV_SQL!A1:INDEX(HPOV_SQL!A:A,D60)=A6))

This form of INDEX is only semi-volatile, so it will recalculate when the workbook is opened (and if any of its inputs are directly changed) but not at every recalculation.

 

[ReproRetro]

Thanks Rory. It came up with a formula error when I tried it. But I will play with it tomorrow

 

[RoryA]

I don't think the original version you posted was correct, and I just tweaked that to use INDEX. I think it should really be:

=SUMPRODUCT(((INT(HPOV_SQL!E1:INDEX(HPOV_SQL!E:E,D60))=B$61)+(HPOV_SQL!G1:INDEX(HPOV_SQL!G:G,D60)=""))*(HPOV_SQL!A1:INDEX(HPOV_SQL!A:A,D60)=A6))

 

[ReproRetro]

Thanks Rory, I just got it to work. It's:
=SUMPRODUCT((INT(HPOV_SQL!E1:INDEX(HPOV_SQL!E:E,D60))<B$61)*((INT(HPOV_SQL!G1:INDEX(HPOV_SQL!G:G,D60)>=B$61))+(HPOV_SQL!G1:INDEX(HPOV_SQL!G:G,D60)=""))*(HPOV_SQL!A1:INDEX(HPOV_SQL!A:A,D60)=A6))

And it doesn't slow the coding.
You are a legend, I would never have thought of using INDEX
Thank you

 

[RoryA]

Glad to help, and thanks.