Enable iterative calculation issue

Question

Hello Microsoft Community,

I have created shared excel file on SharePoint, in which I have some formula having own cell reference hence I am getting circular formula error. And to solve that error I have tick marked Enable iterative calculation as highlighted in below image. But this tick automatically disable everyday meaning everyday I have tick again.

how we can resolve this issue? Means tick on "enable iterative calculation" once not everyday.

Thank you in advance

Answer 1

Hi Parikh Anish,

Greetings! Thank you for posting to Microsoft Community.

It looks design behavior for iterative. Suggest Send feedback to Microsoft

Excel will apply/use the calculation settings of the first workbook you open in a session. So, if you first open workbook A (which had iteration disabled when it was last saved) and then Workbook B (with iteration enabled when it was last saved), Excel will keep iteration disabled.

As workaround, save the file as xlsm(Macro enabled workbook), Then copy the code to "this workbook".

This code will set the iteration enabled when the workbook is open.

Private Sub Workbook_Open() Application.Iteration = True End Sub

Best Regards,

Snow Lu

Answer 2

Hello Snow Lu,
Thank you very much for your respond.

Just wondering, will macro work on SharePoint shared file?
Because I have tried but macro is not working on online 365.

Is there any other way for doing this?

Thank you

Best Regards,

Anish Parikh

Answer 3

You cannot run macro in the Excel online.

Could you share us the formula and purpose for calculate itself? It may some other way to accomplish it.

Answer 4

Hello Snow Lu,
Thank you very much for your respond.

Here is my formula: =IF($A4<>"",IF(E4="",TODAY(),E4),"")

Output from Formula: If X person make entry in column A cells then automatically date should come at column E cells and after that it should not be changed.

Is there any other way to get the this?

Thank you in advance

Best Regards,

Anish Parikh

Answer 5

No other way to get unchangeable date from formula without interative calculation.

It is suggested to manually input the date instead of using formula beacuse of the limitaion of interative calculation settings.