A sheet stops working intermittently

A sheet in my project stops working every now and then, and all the sheets and dashboards referencing this sheet stop showing data.

In this state, when the the sheet is opened, cells with formulas show "#INVALID VALUE". After one or two seconds, these errors disappear and correct values as per formulas are shown. Moreover, the save button gets enabled. If the sheet is saved, it starts working normally.

The problem occurs on irregular intervals which vary from few hours to a day or more.

A snapshot of the sheet is shown below:

All columns are of "Text/Number" type. The "Start Date" and "End Date" rows have dates in text format. The "Total Days" and "Net Workdays" rows calculate intervals by using NETDAYS and NETWORKDAYS formulas.

Example: =NETDAYS(DATE(VALUE(MID([1M1]2, 7, 2)), VALUE(MID([1M1]2, 4, 2)), VALUE(LEFT([1M1]2, 2))), DATE(VALUE(MID([1M1]3, 7, 2)), VALUE(MID([1M1]3, 4, 2)), VALUE(LEFT([1M1]3, 2))))

Best Answer

  • Julio S.
    Julio S. Moderator
    Answer ✓

    Hi @Adnan Mustafa,

    The fact that the "Save" button appears when the issues seem to auto-resolve would indicate that actual changes are being made into the sheet. If you aren't fully aware of what those changes might be, you may want to review the Activity Log and cell history of the affected cells to better understand those changes as well as any other possible ones that might be causing the formulas to error out. Based on the type of error received, I would pay special attention to possible changes in the Column properties.

    If this behavior persists and you can't find specific actions in the Activity Log that could trigger it, I would recommend opening a ticket with our Support team via this form

    I hope this can be of help.

    Cheers!

    Julio

Answers

  • Julio S.
    Julio S. Moderator
    Answer ✓

    Hi @Adnan Mustafa,

    The fact that the "Save" button appears when the issues seem to auto-resolve would indicate that actual changes are being made into the sheet. If you aren't fully aware of what those changes might be, you may want to review the Activity Log and cell history of the affected cells to better understand those changes as well as any other possible ones that might be causing the formulas to error out. Based on the type of error received, I would pay special attention to possible changes in the Column properties.

    If this behavior persists and you can't find specific actions in the Activity Log that could trigger it, I would recommend opening a ticket with our Support team via this form

    I hope this can be of help.

    Cheers!

    Julio

  • Activity log looks normal, but cell history of affected cells show that the cell value changes and then reverts back on its own. This explains the enabling of save button. I will open a ticket if the behavior persists.

    Thank you very much Julios. You have helped to make some sense out of a bizarre situation.