Disabling circular reference

  • Thread starter Thread starter CiaraG
  • Start date Start date
C

CiaraG

Good Afternoon All,

I have a workbook were the circular reference check has
been disabled ie. within the Tools/Options/Calculation
Tab - the Iteration CheckBox is ticked.

However, if another MS Exel file is opened prior to
opening my workbook, then the circular reference check is
activated.

Does anyone have any idea how I can prevent this from
happening??

Thanks

Ciara
 
Yep. We have the same issue at work, where one of our overheads on a certain
type of contract is a % of the Sell Price, hence a circular reference when
dealing with spreadsheet versions of that particular pricing model. We get the
error message and then have to go set it manually, so it's just easier to let
the code do it rather than have people go play with their settings and
potentially screw it up. It's a right royal PITA to contend with, and that was
one of the ways we handled it, and whilst not pretty because you get the
message, at least the setting does get changed correctly. OP could also
possibly put the same thing in their personal.xls and avoid the error message as
long as they are prepared to live without unwanted circs getting flagged, but
possibly not such a good idea.

Now this assumes that it will work that way on their data of course :-) Ours
still evaluates properly ( At least as much as an iterative calc can :-> ), even
when enabled after the error, but I guess it would need checking that everything
worked properly before relying on it.
 
I haven't really thought about this at work in ages, but now it's cropped up,
I'm thinking one way of killing the message entirely would be to use the
before_close event to actually break the circ, take off the iterations, save the
book and then finally close. Then just have the open_event set the iterations
and then rebuild the circ. That should kill any error messages at all shouldn't
it?
 
Yep. We have the same issue at work, where one of our overheads on a certain
type of contract is a % of the Sell Price, hence a circular reference when
dealing with spreadsheet versions of that particular pricing model. . . .
Click to expand...

If Overhead is x% of SellPrice, is the problem that SellPrice is a function of
cost of goods and services sold + overhead + profit, so that Overhead and
SellPrice appear to be functions of each other? If so, and if profit is y% of
SellPrice (just to add another complication), then

SellPrice = COG&SS / [100% - x% -y%]

Overhead = x% * COG&SS / [100% - x% -y%]

Profit = y% * COG&SS / [100% - x% -y%]
 
Hi Harlan - O/H in question is just one of a number of subset O/Hs that make up
COGS, though I didn't have the issue you highlighted with y. Happy to take it
offline so you can see what's going on if you were interested, but I can't
really post any more on this in here for obvious reasons. I do have a formulaic
method of achieving the desired output that one of our mathematicians came up
with, but the model is a historical one from before my time with a s%$t load of
sheets and dependencies across multiple years and escalation rates etc, that to
be perfectly honest, given that an iterative approach works, I just never really
had the incentive to try and fix the damn thing properly. Was just always
easier to set iterations on and use the circ :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Harlan Grove said:
Yep. We have the same issue at work, where one of our overheads on a certain
type of contract is a % of the Sell Price, hence a circular reference when
dealing with spreadsheet versions of that particular pricing model. . . .
Click to expand...

If Overhead is x% of SellPrice, is the problem that SellPrice is a function of
cost of goods and services sold + overhead + profit, so that Overhead and
SellPrice appear to be functions of each other? If so, and if profit is y% of
SellPrice (just to add another complication), then

SellPrice = COG&SS / [100% - x% -y%]

Overhead = x% * COG&SS / [100% - x% -y%]

Profit = y% * COG&SS / [100% - x% -y%]
Click to expand...
 
Back
Top