Try entering a value in A2 then enter the formula above in A1. The formula will return the number in A2 but it will not update again when A2 is changed unless you force a recalculation. I guess this is because A2 is not referred to in the formula so does not form part of the calculation chain.

Reopening the workbook restores the same different values in A1 and A2, but inserting :INDEX(A1,1) or :IF(1,A1) after A1 will cause the formula to recalculate after opening so that A1 equals A2.

This demonstrates Charles’s point above that dynamic ranges recalculate each time the workbook is opened.

]]>what about this I just found:

=OFFSET(BY160,0,0)

cewl

It can be worse. I just found

=HYPERLINK(“#”&CELL(“address”,OFFSET($X$1,0,0)),”x”)

cewwler!!

]]>ROFL – sorry Harlan that just cracked me up.

]]>“If OFFSET were returning references to 1000-cell ranges and those were used as arguments to other, slowish functions, the result is slow overall recalc.”

I agree, but sometimes using OFFSET to return a small calculated subset range to an expensive formula is better than using the expensive formula on the full-sized range.

As you point out INDEX is preferable in many cases since its non-volatile, but I have found that on large workbooks it tends to get to the 64K dependency limit (in versions prior to XL2007) faster.

]]>I think it was first noticed because a workbook containing it requested a save when closing even when there had been no recalc.

I think you can use a Calculate event message to determine when the recalc at open occurs, then compare different forms of Index.

I must have a test workbook somewhere …

I used your VolatileFunc.xls to test volatility of Index

The counter did not change on F9.

But is there a way to determine if Index recalculates on Workbook open

]]>