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?
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
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 :(
 

Mike Blackman

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

ADVERTISEMENT

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".
 

XLXRider

Board Regular
Joined
Jul 31, 2004
Messages
180
...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
 

Mike Blackman

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

ADVERTISEMENT

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.
 

XLXRider

Board Regular
Joined
Jul 31, 2004
Messages
180
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
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Mikey

How about fixing the formulas that result in the circular ref? Care to post what you are currently using...
 

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,610
Members
414,080
Latest member
penguin23

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
Top