Can Circular Reference Warnings be supressed on Workbook Open?

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
Hi All,

Just a quick one, hopefully.

I'd have thought the answer would be no, but is there a way of supressing the Circular Reference Warning on Workbook Open?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi Andrew,

Thanks for that, unortunately I don't think many of the Users would realise why the sheet doesn't calculate and that'll probably clog up my email box :(
 
Upvote 0
Can't really, only when the model is empty of certain data is the Circ Ref an issue, once the report has data in then the Named Ranges are re-set within the code and the Circ Ref dis-appears. This is simply a "Wouldn't it be nice if that didn't come up kind of problem".
 
Upvote 0
...or on Workbook_open, have a message box pop up that says something like: "Workbook recalculation has been set to "Manual" mode. You must press the F9 Key to calculate your sheet." And leave the message box clickable to remove it, that way your users will not say "I didn't see the message."

See my signature line, and you'll know why I have to resort to this kind of stuff! (y)

Regards,
XLXRider
 
Upvote 0
Hi,

Again no, the model used to be set to manual before I got my hands on it, but that was purely doen to in-efficint formula and code. One of the main benefits of my re-build is that it can function in half the time the previous version functioned on manual whilst on Automatic.

Not a route I will be taking, as per your signature, couldn't agree more. Problem with Super-Idiots are that half of them don't even know how you set the Calcualtion back to automatic.

Cheers anyway.
 
Upvote 0
Hmmmm... you could set the calculation to manual on workbook_open, then change it to automatic with a worksheet_change event (I think that's the one, brain is slow this morning!) That way, as soon as one entry is made to the sheet the calculation changes back to automatic and your users are none the wiser. This may be a solution!

Regards,
XLX
 
Upvote 0
Hi Richard,

Quite happy to have the error message rather than dive back into the formula :)

Some examples;

{=IF($H12="Average",IF(OR(Today<K$11,ISERROR(AVERAGE(K11:K$12))),"-",AVERAGE(K11:K$12)),IF($H12="","",IF(OR(Today < K$11,L$11 < $C12,K$11 > $D12),"-",IF($A$8="VZB",1-(SUM(IF((Priority=1)*(CircID=$A12)*(OutageCauseTag="VZB")*(DateClosed>=K$11)*(DateClosed < L$11),OutTimeMins)/(Dashboard!D$33*24*60))),IF($A$8="Customer",1-(SUM(IF((Priority=1)*(CircID=$A12)*(OutageCauseTag <> "VZB")*(DateClosed >= K$11)*(DateClosed < L$11),OutTimeMins)/(Dashboard!D$33*24*60))),1-(SUM(IF((Priority=1)*(CircID=$A12)*(DateClosed >= K$11)*(DateClosed < L$11),OutTimeMins)/(Dashboard!D$33*24*60))))))))}

=IF($H12="","",IF(OR(Today < J$11,K$11 < 'Lookup Data'!$J2,J$11 > =IF('Lookup Data'!$K2="","",'Lookup Data'!$K2)),"-",IF($H12="Total",SUM(J11:J$12),IF(OR($B12="IP",$B12="DSL",$B12="Ethernet"),MIN((ROUNDUP(SUMPRODUCT(--(CircID=$A12),--(Priority=1),--(OutageCauseTag="VZB"),--(DateClosed >= J$11),--(DateClosed < K$11),--(DateClosed >= 'Lookup Data'!$J2),--(DateClosed < IF('Lookup Data'!$K2="","",'Lookup Data'!$K2)),OutTimeMins)/60,0)*$G12)/30,$G12),$G12*VLOOKUP(SUMPRODUCT(--(CircID=$A12),--(Priority=1),--(OutageCauseTag="VZB"),--(DateClosed >= J$11),--(DateClosed < K$11),--(DateClosed >= 'Lookup Data'!$J2),--(DateClosed < IF('Lookup Data'!$K2="","",'Lookup Data'!$K2)),OutTimeMins),SLA,MATCH($C12,Percent,0)+2,1)))))

As I say, once the user uploads there previous version into the file or imports some data into a new report the error is gone, its just the way I've used VBA to dynamically sey my Named Ranges.
 
Upvote 0

Forum statistics

Threads
1,217,328
Messages
6,135,919
Members
449,972
Latest member
Natejack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top