Multiple use of dynamic named ranges : expensive?

[Yard]

Hi all,

Have a reasonably busy workbook with a number of dynamic named ranges which are referred to by many formulas in many sheets.

I've already converted them from an OFFSET style to an INDEX style in the belief that this will aid calculation speed (it made a small difference ).

Now I wonder whether the multiple references to the named ranges causes multiple calculations on each calculation event.

e.g. named range "ProductHorizon" which refers to a varying number of cells in each different row - does this named range have to calculate each time the sheet calculates? Therefore would I be better putting the INDEX calculation in the cell, which presumably returns to being non-volatile?

Basic question : if I refer to a dynamic named range once or 100 times, does Excel calculate it once or 100 times?

Thanks
Yard

 

[DonkeyOte]

Perhaps FastExcel will see this post, if not here is his site - in particular see the last paragraph on this page: http://www.decisionmodels.com/calcsecretsb.htm

 

[Yard]

Excellent - thanks.

Explains the sudden slowdown in calculation when I converted a number of formulas to named range references.

Engage reverse gear....