I have a large spreadsheet that caculates a loan in which negative cashflows
are added back to the loan. This is necessarily circular. The spreadsheet
works fine usually, but sometimes the circular itteration quits working and I
get #Value errors. If I remove and re-type the cells that have the circular
reference, all is well and the cells caculate correctly.

This spreadsheet has several hundred circular references to resolve plus 2
or three tables based on those circular references. Is the number of
circular references the problem? Is there a work around?

Do you have workbook calculation set to manual?

Do the errors disappear if you hit F9?

If it's set to manual, go to Tools--Options--Calculation and select
automatic. I'm not very familiar with circular references, though, so if the
above is not the issue, I'm no help to you, sorry.

Dave
--
Brevity is the soul of wit.


"atompson" wrote:

I have a large spreadsheet that caculates a loan in which negative cashflows
are added back to the loan. This is necessarily circular. The spreadsheet
works fine usually, but sometimes the circular itteration quits working and I
get #Value errors. If I remove and re-type the cells that have the circular
reference, all is well and the cells caculate correctly.

This spreadsheet has several hundred circular references to resolve plus 2
or three tables based on those circular references. Is the number of
circular references the problem? Is there a work around?

Yes and Yes. Errors persisit with calculation on Automatic or Manual.

"Dave F" wrote:

Do you have workbook calculation set to manual?

Do the errors disappear if you hit F9?

If it's set to manual, go to Tools--Options--Calculation and select
automatic. I'm not very familiar with circular references, though, so if the
above is not the issue, I'm no help to you, sorry.

Dave
--
Brevity is the soul of wit.


"atompson" wrote:

I have a large spreadsheet that caculates a loan in which negative cashflows
are added back to the loan. This is necessarily circular. The spreadsheet
works fine usually, but sometimes the circular itteration quits working and I
get #Value errors. If I remove and re-type the cells that have the circular
reference, all is well and the cells caculate correctly.

This spreadsheet has several hundred circular references to resolve plus 2
or three tables based on those circular references. Is the number of
circular references the problem? Is there a work around?

I assume you have the iteration (tools/options/calculation) set pretty high?

"atompson" wrote:

Yes and Yes. Errors persisit with calculation on Automatic or Manual.

"Dave F" wrote:

Do you have workbook calculation set to manual?

Do the errors disappear if you hit F9?

If it's set to manual, go to Tools--Options--Calculation and select
automatic. I'm not very familiar with circular references, though, so if the
above is not the issue, I'm no help to you, sorry.

Dave
--
Brevity is the soul of wit.


"atompson" wrote:

I have a large spreadsheet that caculates a loan in which negative cashflows
are added back to the loan. This is necessarily circular. The spreadsheet
works fine usually, but sometimes the circular itteration quits working and I
get #Value errors. If I remove and re-type the cells that have the circular
reference, all is well and the cells caculate correctly.

This spreadsheet has several hundred circular references to resolve plus 2
or three tables based on those circular references. Is the number of
circular references the problem? Is there a work around?