Just curious if people have a better way of doing what I'm trying to,
or whether there is a solution to problems I'm encoutering...

I've created a production schedule which forecasts production over a
number of years. It is based on a number of variables the user can
change. However, as actual data is entered, I've tried to make the
forumla's switch to the actual data to make forecasts more accurate.
For example, in K35, I have the formula:
=IF(ISBLANK(B35),"",IF(HASFORMULA(J35), K$32, J35/C35)). The
'HasFormula' function is one I adapted off the net, and returns a
boolean depending on whether the target cell has a formula. As the real
data in entered, it switches.... fairly straight forward I think.

The problem is that there is a circular reference of sorts. While it
never occurs at the same time, it is possible for the value in 2 or 3
different cells to be circularly dependent on each other (one relies on
the other when a formula is involved, then on the other when actual
data is entered). It all works fine normally, but a problem occurs when
I hide the cells using a macro and then redisplay them. I get a #VALUE!
error, citing a circular reference. I delete the formula, press undo
and it refreshes and is back to working. Another interesting thing is
when I hide these rows manually and redisplay them, there is no
problem. The Macro is an exact recording of the manual process.

So my questions a
1) Is there a better way of doing the adaption between forecasting and
real data - the idea of a cells value changing depending on whether
surrounding cells are formula's or actual values.
2) Has anyone got any ideas how to stop the error from occuring -
perhaps turning off the circular reference checking or something??
3) Why would the recorded Macro be a problem but doing it manually is
fine?
4) If a tree falls in the forest, and there is no-one there to hear it,
is it really a tree??

Thanks for all ideas,

Cheers

Reg