|
Disabling circular reference
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 |
Disabling circular reference
The options for calculate are set by the first file opened by excel when you
start it. I believe you would need to have two files - one that the user opens and which has code to set the iteration option, then which opens your current workbook with that code. -- Regards, Tom Ogilvy "CiaraG" wrote in message ... 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 |
Disabling circular reference
How about the following in the ThisWorkbook module of the workbook that needs
the iterations:- Private Sub Workbook_Open() With Application .Iteration = True .MaxChange = 0.001 End With End Sub and perhaps the opposite to shut it off when it is closed -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Tom Ogilvy" wrote in message ... The options for calculate are set by the first file opened by excel when you start it. I believe you would need to have two files - one that the user opens and which has code to set the iteration option, then which opens your current workbook with that code. -- Regards, Tom Ogilvy "CiaraG" wrote in message ... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.608 / Virus Database: 388 - Release Date: 03/03/2004 |
Disabling circular reference
The workbook open event fires after the error has been displayed to the
user. Is that what you meant? Open the workbook, respond to the error message, then the workbook open event will set the calculation mode? -- Regards, Tom Ogilvy "Ken Wright" wrote in message ... How about the following in the ThisWorkbook module of the workbook that needs the iterations:- Private Sub Workbook_Open() With Application .Iteration = True .MaxChange = 0.001 End With End Sub and perhaps the opposite to shut it off when it is closed -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "Tom Ogilvy" wrote in message ... The options for calculate are set by the first file opened by excel when you start it. I believe you would need to have two files - one that the user opens and which has code to set the iteration option, then which opens your current workbook with that code. -- Regards, Tom Ogilvy "CiaraG" wrote in message ... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.608 / Virus Database: 388 - Release Date: 03/03/2004 |
Disabling circular reference
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. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Tom Ogilvy" wrote in message ... The workbook open event fires after the error has been displayed to the user. Is that what you meant? Open the workbook, respond to the error message, then the workbook open event will set the calculation mode? -- Regards, Tom Ogilvy "Ken Wright" wrote in message ... How about the following in the ThisWorkbook module of the workbook that needs the iterations:- Private Sub Workbook_Open() With Application .Iteration = True .MaxChange = 0.001 End With End Sub and perhaps the opposite to shut it off when it is closed -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "Tom Ogilvy" wrote in message ... The options for calculate are set by the first file opened by excel when you start it. I believe you would need to have two files - one that the user opens and which has code to set the iteration option, then which opens your current workbook with that code. -- Regards, Tom Ogilvy "CiaraG" wrote in message ... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.608 / Virus Database: 388 - Release Date: 03/03/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.608 / Virus Database: 388 - Release Date: 03/03/2004 |
Disabling circular reference
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? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Ken Wright" wrote in message ... 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. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Tom Ogilvy" wrote in message ... The workbook open event fires after the error has been displayed to the user. Is that what you meant? Open the workbook, respond to the error message, then the workbook open event will set the calculation mode? -- Regards, Tom Ogilvy "Ken Wright" wrote in message ... How about the following in the ThisWorkbook module of the workbook that needs the iterations:- Private Sub Workbook_Open() With Application .Iteration = True .MaxChange = 0.001 End With End Sub and perhaps the opposite to shut it off when it is closed -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "Tom Ogilvy" wrote in message ... The options for calculate are set by the first file opened by excel when you start it. I believe you would need to have two files - one that the user opens and which has code to set the iteration option, then which opens your current workbook with that code. -- Regards, Tom Ogilvy "CiaraG" wrote in message ... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.608 / Virus Database: 388 - Release Date: 03/03/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.608 / Virus Database: 388 - Release Date: 03/03/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.608 / Virus Database: 388 - Release Date: 03/03/2004 |
Disabling circular reference
"Ken Wright" wrote...
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. . . . 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%] -- To top-post is human, to bottom-post and snip is sublime. |
Disabling circular reference
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" wrote in message ... "Ken Wright" wrote... 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. . . . 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%] -- To top-post is human, to bottom-post and snip is sublime. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.608 / Virus Database: 388 - Release Date: 04/03/2004 |
| All times are GMT +1. The time now is 01:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com