Calculate() throws error : Circular reference

Mar 6, 2015 at 8:12 AM
HI, Have a sheet which have lot of formulas. When I invoke calculate on sheet or on particular cell I get Circular reference error.
But formulas does not have circular reference.

Formula with Cell AJ19:
=IF(ROWS(AJ$18:AJ19)=2,R19,IF(AI19=0,AJ18+R19-(G20="")SUM(AF:AF),MAX(AJ18-AI19-(G20="")SUM(AF:AF),0)))

Please help.
Mar 6, 2015 at 9:19 AM
Edited Mar 6, 2015 at 10:51 AM
[Edited : Corrected]Downloaded the Code and figured out that ROWS function is causing the issue as it is referring same range.
Developer
Mar 8, 2015 at 6:10 AM
You can try to set the property AllowCircularReference to true in ExcelCalculationOption (there are overloads on the Calculate methods that accepts this class as an argument).
Mar 8, 2015 at 2:39 PM
I tried with that, but it fails at this point.

if (adr.WorkSheet == null && adr.Collide(new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column))!=ExcelAddressBase.eAddressCollition.No)
                    {
                        throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}", ExcelAddressBase.GetAddress(f.Row, f.Column))));
                    }
anyway I solved by updating my formula with sheet. and Have other issue and is quite starnge . Is there way I can send you my excel to have a look ?
Developer
Mar 13, 2015 at 2:35 PM
Hi, yes you can create an issue here on codeplex and attach your workbook to that issue.