Hi Andy,

I spotted your other post and thought it a pretty interesting problem.
I've tried a few things but keep on getting nowhere.

How did Tim's suggestion go?

My idea, which might not be doable, was for each cell in the
Range("A1:C100") on sheet2 look for its dependents (cells that contain
a formula that refer to it). I spotted the Dependents property in the
Object Browser and Help states "Returns a Range object that represents
the range containing all the dependents of a cell". However, I've never
used it before and have been struggling to make use of it. My main
problem is dealing with the error that occurs when there aren't any
dependents.

If I manage to build up a Range of all the dependents for the
Range("A1:C100") I will then try the following for each dependent..

copy the formula to a string variable, then put the formula back into
the dependent cell using...

FormulaArray = the string variable value

and having autocalculation on (but then that will probably result in
the time delay again!?)

I got this expression using the macro recorder for just pressing Ctrl +
Shift + Enter when a cell with an array formula is entered
(Selection.FormulaArray = string for formula)


I could be barking up the wrong tree entirely or I'm doing something
silly when I try to deal with the error resulting from asking for the
dependents of a cell that has none.


Ken Johnson