Hi Kris,

Just one thought

Consider to calculate in a macro. That macro would also have to create the
correct Defined Name (and delete the old definition first, of course). So
technically it is not a dynamic name, but it will be adjusted each time you
calculate, as long as you use just the macro for that.

--
Kind regards,

Niek Otten

"Kris_Wright_77" wrote in message
...
About a year ago I created a giant spreadsheet that took a large download
from a Financial database, and then sorted it into various categories
across
time, using a series of SUMIF functions that specified whole columns
(almost)
Row 2 to 65536.
And the whole spreadsheet would recalculate itself within about 5 - 10
minutes (getting slower with the increase in data downloaded each month).

However, over the last year I have learnt a lot of useful tips from this
discussion group and various links to other webpages.
One of which says that it is Better to use Dynamic Ranges to specify
columns/areas of data that change size by defining a Named Range that uses
the OFFSET( , , ,COUNTA()) formula.
Which has worked great for other spreadsheets I have created recently,
especially in combination with the SUMPRODUCT() formula.

However, when I tried to improve upon my original spreadsheet that was
using
the SUMIF formulaes by replacing them all with a SUMPRODUCT() with Dynamic
Ranges the sheet takes absolutley forever to recalculate.

Could someone give me some advice as to whether it is better to
Revert back to my original SUMIF type formulas
OR
Use the SUMPRODUCT with Non-Dynamic Ranges (ie Row 2 to Row 65536)
OR
Use the SUMPRODUCT with Non-Dynamic Range with a Find & Replace Macro to
Specify the Columns Ranges (although when I have run a Find and Replace
manually it takes about 30mins to run - there are a lot of repetitive
formulae)
OR
Something Else that reduces the Volatility of the Formulae (this was cited
as a major contributor to slowing recalculations down)
I presume that the Named Dynamic Ranges get recalculated everytime a Cell
recalculates, which is the reason the revised spreadsheet runs
significantly
slower.
If this is the case, is there a method to overcome this problem??

Thanks for any help that you can give.

Kris