Can Circular Reference Warnings be supressed on Workbook Open?

[Mike Blackman]

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?

 

[Andrew Poulsom]

You won't get the warning if calculation is set to manual.

 

[Mike Blackman]

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

 

[Andrew Poulsom]

Better remove the circular reference then.

 

[Mike Blackman]

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]

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

Regards,
XLXRider

 

[Mike Blackman]

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]

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]

Mikey

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

 

[Mike Blackman]

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.